Set VBA Range Variable to A Set of Cells in Another Workbook

Question

Question: I have workbook say workbook A in which I have inserted a module containing a VBA macro. Within that macro I have declared a set of ranges - for example, dim rngName as Range. The macro opens a file in a specified folder, I do not know the name of the this file. I want to set rngName to a range from the file I just opened. How do I do that ?

Sample code I what I have written:

Public Sub Foo()

Dim rngName As Range
Dim lastRow as Long 

Code Open A Workbook  'and so this should be the active workbook after opening ?

With ActiveWorkbook  
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  Set rngName = Range("A1" & LastRow).Cells

End With

End Sub

I do know the variable lastRow is assigned the correct value. Note my script does not produce an error however nothing is assigned to the ranges.

Cheers

Scott Craner Scott Craner · Accepted Answer · 2020-04-08T21:59:38

Do not assume that the newly opened workbook is the active workbook. Assign it to a variable.
You need to designate a worksheet.
should be .Range("A1:A" & lastRow)

Public Sub Foo()
    Dim wkb As Workbook
    Set wkb = Workbooks.Open("filename")

    With wkb.Worksheets("Sheet1") 'Change to your sheet name
        Dim lastRow As Long
        lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Dim rngName As Range
        Set rngName = .Range("A1:A" & lastRow).Cells
    End With

    Debug.Print rngName.Address
End Sub

Set VBA Range Variable to A Set of Cells in Another Workbook

1 Answers