CODEWITHSUNDEEP
excelvba
GalacticPonderer
GalacticPonderer

Reputation: 547

Copy a range from one workbook to another

I want to copy data from one workbook to another.

The following line works:

Workbooks(TempBook.Name).Worksheets(sheet_Index).Range("A1:A20").Copy _
  Workbooks(mainWB.Name).Worksheets(sheet_Index).Range("A1")

This throws

Application-defined or object-defined

Workbooks(TempBook.Name).Worksheets(sheet_Index).Range("A1", Range("A1").End(xlDown)).Copy _
  Workbooks(mainWB.Name).Worksheets(sheet_Index).Range("A1")

Why can I not select a dynamic range?

Upvotes: 0

Views: 93

Answers (1)

BigBen
BigBen

Reputation: 49998

  1. Range("A1").End(xlDown) needs to be qualified with the worksheet and workbook, otherwise it's implicitly on the ActiveSheet.
  2. Workbooks(TempBook.Name) should just be TempBook. Similarly for Workbooks(mainWB.Name).
  3. Side note: you might consider using xlUp to find the last row instead of xlDown.
With TempBook.Worksheets(sheet_Index)
    .Range("A1", .Range("A1").End(xlDown)).Copy _
        Destination:=mainWb.Worksheets(sheet_Index).Range("A1")
End With

Upvotes: 5

Related Questions