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
Bookmarks