VBA IF Statement to reference a cell in another workbook

[Soulidarity]

Hi All,

Please be patient with me, I am a novice with VBA. I am trying to use the following code to check if a cell in the current workbook = a cell in another workbook (which is open) then if it does, carry out a copy and paste function. I can do this easily if the data is in one spreadsheet but I have never done it before where it involves another spreadsheet. Any help would be greatly appreciated.

Dim Path1 As String
Dim myfilename As String
Path1 = Range("I4")
Path2 = Range("E6")
myfilename = Range("I5")
Workbooks.Open Filename:=Path1 & "" & Path2 & myfilename

ThisWorkbook.Activate
Application.ScreenUpdating = False
    'CC 1 January
If (Range("E5") =[B] Cell in the workbook opened above[/B]) And (Range("E3") = "January") Then
'Copy data
Sheet1.Select
Range("E155").Select
Selection.Copy
'Paste data
Workbooks("Master PCA SPB Tracker").Activate
Sheet2.Select
Range("B16").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Thank you in advance!

 

[gallen]

The easiest way is Dim a variable as a workbook, then set the variable when you open it. That way you can reference the workbook directly in your code. It also makes the code mush easier to read with fully qualified references to cells.

Something like this:

Dim wb As Workbook 'define variable to hold a workbook

Set wb = Workbooks.Open(Filename:=Path1 & "" & Path2 & myfilename)

Is there just 1 work sheet in the newly opened workbook?

 

[Soulidarity]

Hi Gallen,

Thank you for the response! There are 6 sheets in the workbook, will this change the code you have supplied?

 

[gallen]

Hi,

In that case it's better to go one step further (and always good practice) to have a worksheet variable too.

    Dim wb As Workbook 'define variable to hold a workbook
    Dim ws As Worksheet
    
    Set wb = Workbooks.Open(Filename:=Path1 & "" & Path2 & myfilename)
    
    Set ws = wb.Worksheets("YOUR_SHEET_NAME") 'change name to name of sheet
    
    'then you can use ws to qualify the cell references
    ws.Range ("Your range")