Dynamically Copy Range of Cells from Closed Workbook?

Question

I would like to copy a range of cells in a closed notebook that does not have a static set of rows. I would like to copy it into an active workbook.

I am trying to dynamically copy all entries under the column of F from file 'test.xlsx' from the 'exception' worksheet. The macro runs without issue if there I use static referencing instead. Here is the code that I am running, it gives me a runtime error for the line that copies the data.

Sub GetClassID()

Dim App As New Excel.Application

Dim wsActive As Worksheet
Set wsActive = ThisWorkbook.ActiveSheet

Dim wbImport As Workbook
Set wbImport = App.Workbooks.Open(Filename:="C:\Test.xlsx", 
UpdateLinks:=True, ReadOnly:=True)

wbImport.Worksheets("Exception").Range("F2",Range("F2").end(xldown)).Copy
wsActive.Range("A2").PasteSpecial Paste:=xlPasteFormats
wsActive.Range("A2").PasteSpecial Paste:=xlPasteValues

App.CutCopyMode = False
wbImport.Close SaveChanges:=False
App.Quit

End Sub

Error I get is runtime erorr '1004': Interface not registered

Answer 1

Assuming you run this in an Excel VBA? You don't need to open the other workbook as an Excel.Application, just remove the app out of it and open the workbook normally:

Sub GetClassID()

Dim wsActive As Worksheet
Set wsActive = ThisWorkbook.Sheets("Another Sheet Name")

Dim wbImport As Workbook
Set wbImport = Workbooks.Open(Filename:="C:\Test.xlsx", UpdateLinks:=True, ReadOnly:=True)

With wbImport.Worksheets("Exception")
    .Range("F2", .Range("F2").End(xlDown)).Copy
End With
wsActive.Range("A2").PasteSpecial Paste:=xlPasteFormats
wsActive.Range("A2").PasteSpecial Paste:=xlPasteValues

App.CutCopyMode = False
wbImport.Close SaveChanges:=False
App.Quit

End Sub

Answer 2

In my experience, the most effective way to copy a dynamic range is to create a variable as an integer and assign the row of the last cell to be copied (or column if needing to select a row of data across to a certain point. I usually accomplish it with something like this:

Dim R as Integer
With ThisWorkbook.Worksheets
   R = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

Then you can plug in 'R' for the row number in a range to make it dynamic each time the macro is ran. For instance: .Range("A1:A" & R).Copy would copy the used range in Column A. It also makes it really easy to reference the last row for loops and such continuously throughout your code. Hope this helps!