Apply the same macro to multiple worksheets

[Apple08]

Hi All

I have a macro which works fine on one worksheet "Report" which is set as ws. However it is required to run on multiple worksheets with tab names: "Unit", "Training", "Projects". ws1 remains unchanged to "Log". Please could someone tell me what should I do to update the code below:

Private Sub Reconcile2020()
Dim ws1 As Worksheet, ws As Worksheet, LastRow As Long, LRow As Long, i As Long, r As Long

Set ws = Worksheets("Report")
Set ws1 = Worksheets("Log")

LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
LRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

For i = LRow To 2 Step -1
For r = LastRow To 2 Step -1

If ws1.Cells(i, "A").Value = ws.Cells(r, "A").Value _
And ws1.Cells(i, "B").Value = ws.Cells(r, "B").Value _
And ws1.Cells(i, "F").Value = ws.Cells(r, "F").Value Then

For Each Z In Array("C", "D", "G", "H", "I", "J")

If ws1.Cells(i, Z).Value <> ws.Cells(r, Z).Value Then
ws1.Cells(i, Z).Interior.ColorIndex = 6
ws.Cells(r, Z).Value = ws1.Cells(i, Z).Value

End If

Next Z

End If

Next r
Next i
End Sub

 

[offthelip]

you just need to modify your code like this:

Private Sub Reconcile2020()
Dim ws1 As Worksheet, ws As Worksheet, LastRow As Long, LRow As Long, i As Long, r As Long
Dim wkshts As Variant
wkshts = Array("Report", "Unit", "Training", "Projects")
For w = 0 To UBound(wkshts)

Set ws = Worksheets(wkshts(w))
Set ws1 = Worksheets("Log")
LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
LRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

For i = LRow To 2 Step -1
For r = LastRow To 2 Step -1

If ws1.Cells(i, "A").Value = ws.Cells(r, "A").Value _
And ws1.Cells(i, "B").Value = ws.Cells(r, "B").Value _
And ws1.Cells(i, "F").Value = ws.Cells(r, "F").Value Then

For Each Z In Array("C", "D", "G", "H", "I", "J")

If ws1.Cells(i, Z).Value <> ws.Cells(r, Z).Value Then
ws1.Cells(i, Z).Interior.ColorIndex = 6
ws.Cells(r, Z).Value = ws1.Cells(i, Z).Value

End If

Next Z

End If

Next r
Next i
End If
End Sub

note however your code is very inefficient and is likely to be very slow, the reason for this is that you are accessing the worksheet multiple times in multiple loops (3 now!!). In particular you access the log sheet exactly the same way on each loop, you can make it at least 1000 times faster by loading the values into a variant array and accessing the array each time for the checks. You can't avoid accessing the worksheet to do the formatting but most of the others you can/

 

[Apple08]

Thanks very much. Please could I double check that do I have to add in 'End if' before End Sub. There are only two 'If' statements and there are three End if. I might have missed something. Many thanks.

 

[offthelip]

Sorry that was my mistake, I must have pasted it in from somewhere, delete the last one!!

 

[Fluff]

I think the last End If should actually be Next w

 

[offthelip]

Fluff you are correct, I don't know what happened in my copy and paste but obviously it went wrong!!

 

[Apple08]

Thanks very much both, It works perfectly!