Compare Sheets

J

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
786
Office Version
  1. 365
Hi,

Can somebody please help me to modify the code below instead of changing the sheets name to compare in the code every time want to compare sheets instead to prompt for the sheets names to compare.

Code:

Code: 
 Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range, mydiffs As Long  Dim r1 As Range, r2 As Range
  Set sh1 = Sheets("JUL")
  Set sh2 = Sheets("AUG")
  Set r1 = sh1.Range("A3", sh1.Range("A" & Rows.Count).End(xlUp))
  Set r2 = sh2.Range("A3", sh2.Range("A" & Rows.Count).End(xlUp))
  r1.Interior.Color = vbWhite
  r2.Interior.Color = vbWhite
  
  For Each c In r1
    Set f = r2.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  For Each c In r2
    Set f = r1.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  MsgBox mydiffs & " differences found"
End Sub

thank you
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Maybe something like this:
Code: 
Dim ws1 as String, ws2 as String
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range, mydiffs As Long  
Dim r1 As Range, r2 As Range

ws1 = InputBox("What is the name of the first sheet you wish to compare?")
ws2 = InputBox("What is the name of the second sheet you wish to compare?")

Set sh1 = Sheets(ws1)
Set sh2 = Sheets(ws2)
That should get you started, though you may want to also add some error-coding to make sure that they do not enter an invalid sheet name.
 
Last edited:
Upvote 0
Thank you.

I will try and let you know.

I am not that good in coding would you mind letting me know how would I add
error-coding .
 
Upvote 0
You can incorporate the UDF found here into your code: https://www.thespreadsheetguru.com/the-code-vault/vba-function-determine-if-worksheet-exists

So copy that UDF to your VBA code. Then you can amend the code I posted for you above like this:
Code: 
Dim ws1 As String, ws2 As String
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range, mydiffs As Long
Dim r1 As Range, r2 As Range

ws1 = InputBox("What is the name of the first sheet you wish to compare?")
[COLOR=#ff0000]If WorksheetExists(ws1) = False Then
    MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"
    Exit Sub
End If[/COLOR]

ws2 = InputBox("What is the name of the second sheet you wish to compare?")
[COLOR=#ff0000]If WorksheetExists(ws2) = False Then
    MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"
    Exit Sub
End If[/COLOR]

Set sh1 = Sheets(ws1)
Set sh2 = Sheets(ws2)
 
Upvote 0
Thank you for the help.

I did add the code the problem is keep saying sheet name doesn't exist but it does thing the problem is that the sheets are hidden cause i have code to double click to unhide them so i think that's the problem but the original code I posted above doesn't have that problem if hidden worked the only problem as I as said need modify the code every time to enter names to compare.

here currently code modify as you suggested.

Code: 
Private Function WorksheetExists(ByVal WorksheetName As String) As Boolean'PURPOSE: Determine if a worksheet name exists in the workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault


On Error Resume Next
  WorksheetExists = (ActiveWorkbook.Sheets("2019 Accounts Payable Checklist - NCL.xlsm").Name <> "")
On Error GoTo 0


End Function

Code: 
Sub COMPARE_SHEETS()[COLOR=#ff0000]Dim ws1 As String, ws2 As String[/COLOR]
[COLOR=#ff0000]  Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range, mydiffs As Long[/COLOR]
[COLOR=#ff0000]  Dim r1 As Range, r2 As Range[/COLOR]
[COLOR=#ff0000]
[/COLOR]
[COLOR=#ff0000]  ws1 = InputBox("What is the name of the first sheet you wish to compare?")[/COLOR]
[COLOR=#ff0000]  If WorksheetExists(ws1) = False Then[/COLOR]
[COLOR=#ff0000]  MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"[/COLOR]
[COLOR=#ff0000]  Exit Sub[/COLOR]
[COLOR=#ff0000]  End If[/COLOR]
[COLOR=#ff0000]  ws2 = InputBox("What is the name of the second sheet you wish to compare?")[/COLOR]
[COLOR=#ff0000]  If WorksheetExists(ws2) = False Then[/COLOR]
[COLOR=#ff0000]  MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"[/COLOR]
[COLOR=#ff0000]    Exit Sub[/COLOR]
[COLOR=#ff0000]  End If[/COLOR]


  Set sh1 = Sheets(ws1)
  Set sh2 = Sheets(ws2)
  Set sh1 = Sheets("AUG")
  Set sh2 = Sheets("SUMMARY")
  Set r1 = sh1.Range("A3", sh1.Range("A" & Rows.Count).End(xlUp))
  Set r2 = sh2.Range("A3", sh2.Range("A" & Rows.Count).End(xlUp))
  r1.Interior.Color = vbWhite
  r2.Interior.Color = vbWhite
  
  For Each c In r1
    Set f = r2.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  For Each c In r2
    Set f = r1.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  MsgBox mydiffs & " differences found"
End Sub


Code: 
Sub COMPARE_SHEETS()Dim ws1 As String, ws2 As String
  Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range, mydiffs As Long
  Dim r1 As Range, r2 As Range


  ws1 = InputBox("What is the name of the first sheet you wish to compare?")
  If WorksheetExists(ws1) = False Then
  MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"
  Exit Sub
  End If
  ws2 = InputBox("What is the name of the second sheet you wish to compare?")
  If WorksheetExists(ws2) = False Then
  MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"
    Exit Sub
  End If


  Set sh1 = Sheets(ws1)
  Set sh2 = Sheets(ws2)
  Set sh1 = Sheets("AUG")
  Set sh2 = Sheets("SUMMARY")
  Set r1 = sh1.Range("A3", sh1.Range("A" & Rows.Count).End(xlUp))
  Set r2 = sh2.Range("A3", sh2.Range("A" & Rows.Count).End(xlUp))
  r1.Interior.Color = vbWhite
  r2.Interior.Color = vbWhite
  
  For Each c In r1
    Set f = r2.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  For Each c In r2
    Set f = r1.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  MsgBox mydiffs & " differences found"
End Sub
 
Upvote 0
It works just fine for me, even if the sheets are hidden.

However, I noticed that there is an issue with how you have written your code. You are asking for the inputs, setting the sheet references, but then immediately overwriting them with hard-coded values!
Code: 
  Set sh1 = Sheets(ws1)
  Set sh2 = Sheets(ws2)
[COLOR=#ff0000]  Set sh1 = Sheets("AUG")
  Set sh2 = Sheets("SUMMARY")[/COLOR]
Get rid of the two lines in red!
It is fruitless to ask for inputs if you are just going to overwrite them with hard-coded values.
 
Upvote 0
Hi,

I did still getting same error:

Code: 
Sub COMPARE_SHEETS()Dim ws1 As String, ws2 As String
  Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range, mydiffs As Long
  Dim r1 As Range, r2 As Range


  ws1 = InputBox("What is the name of the first sheet you wish to compare?")
  If WorksheetExists(ws1) = False Then
  MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"
  Exit Sub
  End If
  ws2 = InputBox("What is the name of the second sheet you wish to compare?")
  If WorksheetExists(ws2) = False Then
  MsgBox "That sheet name does not exist!  Please try again!", vbOKOnly, "ERROR!"
    Exit Sub
  End If


  Set sh1 = Sheets(ws1)
  Set sh2 = Sheets(ws2)
[COLOR=#008000]  'Set sh1 = Sheets("AUG")[/COLOR]
[COLOR=#008000]  'Set sh2 = Sheets("SUMMARY")[/COLOR]
  Set r1 = sh1.Range("A3", sh1.Range("A" & Rows.Count).End(xlUp))
  Set r2 = sh2.Range("A3", sh2.Range("A" & Rows.Count).End(xlUp))
  r1.Interior.Color = vbWhite
  r2.Interior.Color = vbWhite
  
  For Each c In r1
    Set f = r2.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  For Each c In r2
    Set f = r1.Find(c, , xlValues, xlWhole)
    If f Is Nothing Then
      c.Interior.Color = vbRed
      mydiffs = mydiffs + 1
    End If
  Next
  MsgBox mydiffs & " differences found"
End Sub
 
Upvote 0
What is the exact error code and verbiage, and which line of code is highlighted when you hit "Debug"?
 
Upvote 0


Don't if you can see the image but he message exactly is ERROR! "That sheet name does not exist! Please try again.

On debug shows this:

Watch : : ws1 = InputBox("What is the name of the first sheet you wish to compare?") : <Out of context> : Empty : Module11.COMPARE_SHEETS
 
Upvote 0
OK, so it is our code that is catching it (and not a processing error).

I look at your post code again, and noticed this:
Code: 
Private Function WorksheetExists(ByVal WorksheetName As String) As Boolean'PURPOSE: Determine if a worksheet name exists in the workbook
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault


On Error Resume Next
  WorksheetExists = (ActiveWorkbook.[COLOR=#ff0000]Sheets("2019 Accounts Payable Checklist - NCL.xlsm")[/COLOR].Name <> "")
On Error GoTo 0


End Function
Why did you hard-code the sheet name in there?
You should just be copying that UDF as-is, without making any changed to it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA to declare column headers and use in further code
Replies
6
Views
466
AggieJess27
A
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
385
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Copy a column value when the values match to multiple sheets of the workbook
Replies
1
Views
442
sofas
S
J
  • Solved
Start Cell "A2"
Replies
9
Views
433
Fluff
Fluff
W
  • Solved
Looking For Tips To Improve My VBA
Replies
8
Views
231
who am i
W

Forum statistics

Threads
1,224,812
Messages
6,181,084
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top