Comparing values from 2 different Workbook Worksheets - 2nd Worksheet will not return Value

[lnagel]

Basically
Opening the 1st Workbook(worksheet) and storing the value of 2 different cells then (this works)
Opening the 2nd Workbook(worksheet) and storing the value of 2 different cells (this does not work)

I can see that the correct workbooks(worksheet) are being opened BUT I cant seem to get the values from the 2nd Worksheet (always returns NULL)

Any help would be appreciated

Dim sourceWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim sourcevalidate As String
Dim sourceFilename As Variant

Dim targetWorkbook As Workbook
Dim targetSheet As Worksheet
Dim targetvalidate As String

Set targetWorkbook = Workbooks.Open("C:\SetBilder\Price Guide - 1955 Topps.xlsx")
Set targetSheet = targetWorkbook.Worksheets(1)
targetSheet.UnProtect
targetvalidate = targetSheet.Range("A4") & targetSheet.Range("A13")
MsgBox "Existing Price Guide Validation String = " & targetvalidate

' Get the new Price Guide Update File
filter = "Excel files (*.xlsx),*.xlsx"
caption = "Select the Price Guide Update File for your 1955 Topps Set"

' Set the location Path for the Updated Price Guide to User Desktop"
Set Shell = CreateObject("WScript.Shell")
DesktopPath = Shell.SpecialFolders("Desktop")
ChDir DesktopPath

' User selects Desktop File here"
sourceFilename = Application.GetOpenFilename(filter, , caption, MultiSelect:=False)
If sourceFilename = "False" Then
    Application.DisplayAlerts = True
    targetSheet.Protect
    Exit Sub
End If

' Open the file that user selected'
Set sourceWorkbook = Application.Workbooks.Open(sourceFilename)
Set sourceSheet = sourceWorkbook.Worksheets(1)
sourcevalidate = sourceSheet.Range("CH4") & sourceSheet.Range("CH13")

' Validate that the Updated Price Guide Selected and the Existing Price Guide are compatible'
MsgBox "Price Guide Validation String (NEWFILE) = " & sourcevalidate (this value always returns NULL )
MsgBox "Price Guide Validation String (CURRENT) = " & targetvalidate (this value always returns expected values)

If sourcevalidate <> targetvalidate Then
    MsgBox "File Mismatch - You have selected an Invalid Price Guide File", , "SetBilder Error"
    targetSheet.Protect
    sourceWorkbook.Close savechanges:=False
    targetWorkbook.Close savechanges:=False
    Exit Sub
End If

 

[Fluff]

Two possible reasons.
1) Do you have any hidden sheets? If so the 1st visible sheet may not be sheets(1)
2) Do you have any values in CH4 & CH13?

 

[lnagel]

No hidden sheets... yes values are in CH4 and CH13. In my test case the values in CH4 and CH13 are identical in both worksheets.

 

[Fluff]

Ok, try

sourceValidate = sourceSheet.Range("CH4").Value & sourceSheet.Range("CH13").Value

If that doesn't work, as a test use the sheet name rather than the sheet index.

 

[lnagel]

targetvalidate = targetSheet.Range("A4") & targetSheet.Range("A13")
sourcevalidate = sourceSheet.Range("CH4") & sourceSheet.Range("CH13")

DOH - So it appears that Identical workbooks will not return identical expected values unless and until one uses identical cell references

gawd - I know I've looked at this for at least 6 hours before it smacked me right in the head

Thanks again fluff - as your suggestions seemed to have jarred some long dead brain cells in me

 

[Fluff]

Glad you figured it out & thanks for the feedback