-1

My purpose is to copy rows from another Excel workbook and paste it back into my original Excel workbook

This code works perfectly well if i remove line 2 and test it on my original Excel workbook that i am working on.

But the moment i add in line 2 to activate another Excel workbook ("Book1"), and when i run the same code, this codes fails at the 3rd line. Error message is "Application-defined or object-defined error". Strangely the first 2 lines works but until the 3rd line it fails. This only happens whenever i try to activate different excel workbook.

I tried to convert the Excel files into same format such as xlsm, xlsx, csv but it still does not work. I tried for hours. Please explain to me what is the error. Thank You

Sub test()

MsgBox ActiveCell.Value
Workbooks("Book1").Activate
Range("a1").End(xlDown).Select ----->>This line is where error occurs
i = ActiveCell.Row
Range(Rows(1), Rows(i)).Copy

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
user13316884
  • 47
  • 6

3 Answers3

2

You put your Sub Test() inside a worksheet module, so the unqualified Range("a1") is resolved against the sheet that contains this code, as opposed to the active sheet. You are thus trying to Select a range on a sheet that is not active.

If you put that code in a standard module, it would have worked because then the unqualified Range would have resolved against the active sheet.

The right thing to do is to qualify the Range calls and to not select anything:

MsgBox ActiveCell.Value
With Workbooks("Book1").Worksheets(1)
  .Range(.Range("A1"), .Range("A1").End(xlDown)).EntireRow.Copy
End With
GSerg
  • 76,472
  • 17
  • 159
  • 346
0

To copy cells or entire ranges from one workbook to the other one, first try to define your variables:

Dim wb1 as Workbook, wb2 as Workbook
Dim sht1 as Worksheet, sht2 as Worksheet
set wb1 = Workbooks("Book1")
set wb2 = Workbooks("Book2")
set sht1 = wb1.sheets("YOUR_SHEET_BOOK1")
set sht2 = wb2.sheets("YOUR_SHEET_BOOK2")

You can then control them easily and make them equal.

With sht1
 sht2.range("A1:A10") = .range("A1:A10") 'range A1:A10 of sheet1 will be the same as sheet2
End with

Or with cells:

With sht1
 for i = 1 to 10
  sht2.cells(i, 1) = .cells(i, 1) 'does exactly the same
 Next
End with

It will be way faster than any copy you could make!

BJ2M
  • 61
  • 4
-2

You need to specify on line 3 which workbook/worksheet you want to use.

Here are a couple options:

Quick fix of your existing code

Since you are already activating the target workbook (instead of merely referring to it), you can just use ActiveSheet (short for ActiveWorkbook.ActiveSheet).

Try this:

Sub test()

MsgBox ActiveCell.Value
Workbooks("Book1").Activate
ActiveSheet.Range("a1").End(xlDown).Select
i = ActiveCell.Row
Range(Rows(1), Rows(i)).Copy

End Sub

A quicker way

However, you could instead specify the range to copy without selecting or activating it. This is much quicker.

Try:

Sub test()

MsgBox ActiveCell.Value
Workbooks("Book1").Sheets(1).Range(Workbooks("Book1").Sheets(1).Range("A1"), Workbooks("Book1").Sheets(1).Range("A1").End(xlDown)).EntireRow.Copy

End Sub
ed2
  • 1,457
  • 1
  • 9
  • 26
  • I run into error at this command (.Range("A1"), .Range("A1").End(xlDown)). The error says "invalid or unqualified reference". Is there any missing information? – user13316884 Oct 17 '20 at 11:47
  • I have edited the answer to fully qualify the range references for row and column aspects. This is overkill and can be done in a tidier fashion, but before tidying it up please try the edited solution and let us know how it went. – ed2 Oct 21 '20 at 02:06