CC10
asked on
Excel macro only works on one sheet
Hello,
I would like this macro to run on sheets 1-4 but it just repeats itself on Sheet1 four times instead of moving on to Sheet 2 etc...
Sub FillColumns1()
Dim r1 As Long, r2 As Long, i As Long
Dim ws As Worksheet
Dim wsCounter As Long
'For wsCount = 1 To ActiveWorkbook.Worksheets. Count
For wsCount = 1 To 4
Set ws = ActiveWorkbook.Worksheets( wsCount)
With ws
r1 = .range("A" & .Rows.Count).End(xlUp).Row
r2 = .range("H" & .Rows.Count).End(xlUp).Row
range(Cells(r2, 8), Cells(r2, 22)).AutoFill range(Cells(r2, 8), Cells(r1, 22))
End With
Next
End Sub
I would like this macro to run on sheets 1-4 but it just repeats itself on Sheet1 four times instead of moving on to Sheet 2 etc...
Sub FillColumns1()
Dim r1 As Long, r2 As Long, i As Long
Dim ws As Worksheet
Dim wsCounter As Long
'For wsCount = 1 To ActiveWorkbook.Worksheets.
For wsCount = 1 To 4
Set ws = ActiveWorkbook.Worksheets(
With ws
r1 = .range("A" & .Rows.Count).End(xlUp).Row
r2 = .range("H" & .Rows.Count).End(xlUp).Row
range(Cells(r2, 8), Cells(r2, 22)).AutoFill range(Cells(r2, 8), Cells(r1, 22))
End With
Next
End Sub
Try
range(ws.Cells(r2, 8), ws.Cells(r2, 22)).AutoFill range(ws.Cells(r2, 8), ws.Cells(r1, 22))
range(ws.Cells(r2, 8), ws.Cells(r2, 22)).AutoFill range(ws.Cells(r2, 8), ws.Cells(r1, 22))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thats fine. Thanks very much.
CC
CC
Dim wsCounter As Long
or
For wsCount = 1 To 4