Compare cell content from two worksheets

J

John

I am importing two separate excel files into a master file that has macros
performing multiple tasks. There is a chance that the operator can select the
wrong second file to import. I want to compare two cells in each of the two
worksheets to determine if they are equal. The first file will be copied into
the worksheet "Item Charts" the second into worksheet "Wire Charts". I need
to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2 in
worksheet "Item Charts". As you can see, I have tried multiple ways and
continue to fail. Problem starts at line "ActiveCell.FormulaR1C1".
This is what I have:

On Error GoTo errorhandler
Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "POS NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be an
Item Chart, please check the file name and start again")
ActiveWorkbook.Close
ThisWorkbook.Activate
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Item Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "CIRCUIT NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a
Wire Chart, please check the file name and start again")
Dim WkbkName As Object
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next
ThisWorkbook.Activate
Selection.ClearContents
Range("A1").Select
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Wire Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next

' ActiveCell.FormulaR1C1 = "=IF(R[1]C<>'Item Charts'!R[1]C)"
' cellcheck = ActiveCell(2, 1)
' If cellcheck <> Worksheets("Item Charts")!Cells("A2") Then
' If Cells("A2") <> Worksheets("Item Charts")!Cells("A2") Then
Range("A2").Select
' If Cell.Value <> Worksheets("Item Charts").Cells("A2").Value Then
Application.ScreenUpdating = True
MsgBox ("These Files Do Not Seem to be the same harness assembly,
please check the file name and start again")
End
End If

Would appreciate any help, thanks.
 
S

STEVE BELL

The general code could look like this:

If Workbooks("WKBK1").Sheets("Item Charts").Range("A2") =
Workbook("WKBK2").Sheets("Wire Charts").Range("A2") then

' your code

Else msgbox "Workbooks not equal"
End if

Cautions:
1. The 2 line If statement should be a single line
2. You must replace the names with the names of your workbooks & names
of your sheets
2a you can use variables for all the names.
3 If A2 is numeric - be prepared to deal with minor decimal
differences.
3a If A2 is text than compare LCase() = LCase() or UCase() = UCase to
avoid capitalization
differences.
Note: it isn't necessary to use select or selection in your code.
--
steveB

Remove "AYN" from email to respond
John said:
I am importing two separate excel files into a master file that has macros
performing multiple tasks. There is a chance that the operator can select
the
wrong second file to import. I want to compare two cells in each of the
two
worksheets to determine if they are equal. The first file will be copied
into
the worksheet "Item Charts" the second into worksheet "Wire Charts". I
need
to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2 in
worksheet "Item Charts". As you can see, I have tried multiple ways and
continue to fail. Problem starts at line "ActiveCell.FormulaR1C1".
This is what I have:

On Error GoTo errorhandler
Workbooks.Open Filename:=Dir(Application.GetOpenFilename),
UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "POS NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be an
Item Chart, please check the file name and start again")
ActiveWorkbook.Close
ThisWorkbook.Activate
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Item Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
Workbooks.Open Filename:=Dir(Application.GetOpenFilename),
UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "CIRCUIT NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a
Wire Chart, please check the file name and start again")
Dim WkbkName As Object
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next
ThisWorkbook.Activate
Selection.ClearContents
Range("A1").Select
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Wire Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next

' ActiveCell.FormulaR1C1 = "=IF(R[1]C<>'Item Charts'!R[1]C)"
' cellcheck = ActiveCell(2, 1)
' If cellcheck <> Worksheets("Item Charts")!Cells("A2") Then
' If Cells("A2") <> Worksheets("Item Charts")!Cells("A2") Then
Range("A2").Select
' If Cell.Value <> Worksheets("Item Charts").Cells("A2").Value Then
Application.ScreenUpdating = True
MsgBox ("These Files Do Not Seem to be the same harness assembly,
please check the file name and start again")
End
End If

Would appreciate any help, thanks.
Click to expand...
 
J

John

Thank you Steve. I modified the names of the workbooks and changed the "=" to
"<>" and it is perfect. I don't know why I had such a problem with this, DUH.
--
John S. Walker


STEVE BELL said:
The general code could look like this:

If Workbooks("WKBK1").Sheets("Item Charts").Range("A2") =
Workbook("WKBK2").Sheets("Wire Charts").Range("A2") then

' your code

Else msgbox "Workbooks not equal"
End if

Cautions:
1. The 2 line If statement should be a single line
2. You must replace the names with the names of your workbooks & names
of your sheets
2a you can use variables for all the names.
3 If A2 is numeric - be prepared to deal with minor decimal
differences.
3a If A2 is text than compare LCase() = LCase() or UCase() = UCase to
avoid capitalization
differences.
Note: it isn't necessary to use select or selection in your code.
--
steveB

Remove "AYN" from email to respond
John said:
I am importing two separate excel files into a master file that has macros
performing multiple tasks. There is a chance that the operator can select
the
wrong second file to import. I want to compare two cells in each of the
two
worksheets to determine if they are equal. The first file will be copied
into
the worksheet "Item Charts" the second into worksheet "Wire Charts". I
need
to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2 in
worksheet "Item Charts". As you can see, I have tried multiple ways and
continue to fail. Problem starts at line "ActiveCell.FormulaR1C1".
This is what I have:

On Error GoTo errorhandler
Workbooks.Open Filename:=Dir(Application.GetOpenFilename),
UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "POS NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be an
Item Chart, please check the file name and start again")
ActiveWorkbook.Close
ThisWorkbook.Activate
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Item Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
Workbooks.Open Filename:=Dir(Application.GetOpenFilename),
UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "CIRCUIT NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a
Wire Chart, please check the file name and start again")
Dim WkbkName As Object
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next
ThisWorkbook.Activate
Selection.ClearContents
Range("A1").Select
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Wire Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next

' ActiveCell.FormulaR1C1 = "=IF(R[1]C<>'Item Charts'!R[1]C)"
' cellcheck = ActiveCell(2, 1)
' If cellcheck <> Worksheets("Item Charts")!Cells("A2") Then
' If Cells("A2") <> Worksheets("Item Charts")!Cells("A2") Then
Range("A2").Select
' If Cell.Value <> Worksheets("Item Charts").Cells("A2").Value Then
Application.ScreenUpdating = True
MsgBox ("These Files Do Not Seem to be the same harness assembly,
please check the file name and start again")
End
End If

Would appreciate any help, thanks.
Click to expand...
Click to expand...
 
S

STEVE BELL

John,

Glad to hear you got it to work!!!

It always gets tricky for me when working with workbooks and worksheets.
Sometimes I have to try many different approaches...
The main trick is getting rid of all the select's....

keep on Exceling...

--
steveB

Remove "AYN" from email to respond
John said:
Thank you Steve. I modified the names of the workbooks and changed the "="
to
"<>" and it is perfect. I don't know why I had such a problem with this,
DUH.
--
John S. Walker


STEVE BELL said:
The general code could look like this:

If Workbooks("WKBK1").Sheets("Item Charts").Range("A2") =
Workbook("WKBK2").Sheets("Wire Charts").Range("A2") then

' your code

Else msgbox "Workbooks not equal"
End if

Cautions:
1. The 2 line If statement should be a single line
2. You must replace the names with the names of your workbooks &
names
of your sheets
2a you can use variables for all the names.
3 If A2 is numeric - be prepared to deal with minor decimal
differences.
3a If A2 is text than compare LCase() = LCase() or UCase() = UCase
to
avoid capitalization
differences.
Note: it isn't necessary to use select or selection in your code.
--
steveB

Remove "AYN" from email to respond
John said:
I am importing two separate excel files into a master file that has
macros
performing multiple tasks. There is a chance that the operator can
select
the
wrong second file to import. I want to compare two cells in each of the
two
worksheets to determine if they are equal. The first file will be
copied
into
the worksheet "Item Charts" the second into worksheet "Wire Charts". I
need
to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2
in
worksheet "Item Charts". As you can see, I have tried multiple ways and
continue to fail. Problem starts at line "ActiveCell.FormulaR1C1".
This is what I have:

On Error GoTo errorhandler
Workbooks.Open Filename:=Dir(Application.GetOpenFilename),
UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "POS NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be
an
Item Chart, please check the file name and start again")
ActiveWorkbook.Close
ThisWorkbook.Activate
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Item Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
Workbooks.Open Filename:=Dir(Application.GetOpenFilename),
UpdateLinks:=0
Range("A1").Select
cellcheck = ActiveCell(1, 1)
If cellcheck <> "CIRCUIT NBR" Then
Application.ScreenUpdating = True
MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a
Wire Chart, please check the file name and start again")
Dim WkbkName As Object
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next
ThisWorkbook.Activate
Selection.ClearContents
Range("A1").Select
End
End If
Cells.Select
Selection.Copy
Windows("QIC_AM_r06 for GSD.xls").Activate
Sheets("Wire Charts").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Cells.Select
Cells.EntireColumn.autofit
Range("A1").Select
For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next

' ActiveCell.FormulaR1C1 = "=IF(R[1]C<>'Item Charts'!R[1]C)"
' cellcheck = ActiveCell(2, 1)
' If cellcheck <> Worksheets("Item Charts")!Cells("A2") Then
' If Cells("A2") <> Worksheets("Item Charts")!Cells("A2") Then
Range("A2").Select
' If Cell.Value <> Worksheets("Item Charts").Cells("A2").Value Then
Application.ScreenUpdating = True
MsgBox ("These Files Do Not Seem to be the same harness
assembly,
please check the file name and start again")
End
End If

Would appreciate any help, thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Import data from two workbooks with similar names 4
VBA for Dependents shortcut menu 0
Pasting a cell into bookmarked table from excel vba 1
Macro to run on all files in a folder but exclude sheets with PivotTable, Pivot Charts and Charts 13
Problem with hyperlink macro 0
Compare two columns in a different worksheet 3
Selection.AutoFilter Issue 1
Unprotect sheet when Saving As 2

Top