problem with looping a code. Please help

R

rakeshbrraag

New member
Joined
Nov 23, 2012
Messages
10
Reaction score
0
Points
0
Excel Version(s)
2013
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.
 
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
 
You must log in or register to reply here.
Back
Top