Apply the same macro to multiple worksheets

Apple08

Active Member
Joined
Nov 1, 2014
Messages
375
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
you just need to modify your code like this:
VBA Code:
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

Active Member
Joined
Nov 1, 2014
Messages
375
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
Sorry that was my mistake, I must have pasted it in from somewhere, delete the last one!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think the last End If should actually be Next w
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,725
Office Version
  1. 2010
Platform
  1. Windows
Fluff you are correct, I don't know what happened in my copy and paste but obviously it went wrong!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,780
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top