0

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?

Community
  • 1
  • 1
GalacticPonderer
  • 497
  • 3
  • 16

1 Answers1

5
  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
BigBen
  • 46,229
  • 7
  • 24
  • 40
  • 1
    I know that I am supposed to "avoid comments like ... 'thanks'", but.... thanks. You don't know how happy that just made me. As a follow on, why is .xlup better than xldown? – GalacticPonderer Sep 16 '20 at 16:56
  • 2
    @JKRH because you could encounter blank rows in the middle, your `xlDown` would stop there, instead `xlUp` will always get the last row since it's coming from the last row in the sheet to the first it finds from below. – Damian Sep 16 '20 at 16:58