(Solved) – Set VBA Range Variable to A Set of Cells in Another Workbook


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.


Leave a Reply

Your email address will not be published. Required fields are marked *