Results 1 to 2 of 2

Thread: problem with looping a code. Please help

  1. #1
    Seeker rakeshbrraag's Avatar
    Join Date
    Nov 2012
    Posts
    10
    Articles
    0
    Excel Version
    2013

    Lightbulb problem with looping a code. Please help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,

    I have a code which has to be executed in all the sheets( in my case there are totally 8 sheets and this total sheets may vary) expect first four sheets .



    Sub test_test()
    Dim lastr As Double
    Dim cols As Double


    cols = Sheets("row").Range("V4").Value * 3 ' Determine how many columns to copy across
    With Sheets("campaingn_1")
    lastr = .UsedRange.Rows.Count ' Last used row in worksheet
    .Range("D1:F" & lastr).Select
    Selection.AutoFill Destination:=Range(Cells(1, 4), Cells(lastr, cols + 3)), Type:=xlFillDefault

    End With
    End Sub



    I tried using for loop, but here in my codes second line i have mentioned the sheet name as Campaingn_1 and i am getting error as the execution is limited to sheet named campaingn_1


    Please help me with the code so that the above code should not only limited to campaingn_1 sheet.

  2. #2
    Code:
    Sub test_test()Dim ws As Worksheet
    Dim lastr As Double
    Dim cols As Double
    
    
        cols = Worksheets("row").Range("V4").Value * 3 ' Determine how many columns to copy across
        For Each ws In Worksheets(Array("campaingn_1", "campaingn_2"))
        
            With ws
    
    
                lastr = .UsedRange.Rows.Count ' Last used row in worksheet
                .Range("D1:F" & lastr).AutoFill Destination:=.Range(.Cells(1, 4), .Cells(lastr, cols + 3)), Type:=xlFillDefault
            End With
        Next ws
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •