0

I'm trying to automate copying data from Workbook x Sheet 1 into Workbook 2 Sheet 2 below the last row with values

To copy selection:

x.Sheets("Sheet1").Range(Range("A9"), Range("A9").End(xlDown).End(xlToRight)).Copy

To paste selection:

y.Sheets("Sheet 2").Range("A923").PasteSpecial

I get an Application.defined or Object.defined error whenever I run it.

I can't seem to get it to work. What am I doing wrong?

JvdV
  • 70,606
  • 8
  • 39
  • 70
aab
  • 1,643
  • 2
  • 13
  • 22
  • What is `x` and what is `y` in your VBA code? – Xukrao Jul 27 '19 at 19:16
  • @Xukrao x is workbook 1 and y is workbook 2 – aab Jul 27 '19 at 19:18
  • The two instances of `Range("A9")` are unqualified - you need to specify the workbook and worksheet they are in/on. [This question](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) is similar even though it uses `Cells` instead of `Range`. – BigBen Jul 27 '19 at 19:20
  • @BigBen I want to copy all rows and columns with data starting from cell A9, from workobook 1 (x) to workbook 2 (y). – aab Jul 27 '19 at 19:23
  • I'm just trying to point out the cause of the error. I think the accepted answer to the question I linked to is a good explanation of why you get the error, as well as a possible solution. – BigBen Jul 27 '19 at 19:29
  • @BigBen thanks for pointing that out, I missed the link. I'm not very familiar with vba. – aab Jul 27 '19 at 19:41

1 Answers1

2

The Range function expects a reference to the sheet. You specified the sheet in the first entry, but you should mention it in every Range method.

Please see code below:

x.Sheets("Sheet1").Range(x.Sheets("Sheet1").Range("A9"), x.Sheets("Sheet1").Range("A9").End(xlDown).End(xlToRight)).Copy

Doing this is not recommended as it gets tough to read the code, so you can save the sheet in a variable adn use the variable further like below:

Dim Main_Sheet as Worksheet

Set Main_Sheet = x.Sheets("Sheet1")

Main_Sheet.Range(Main_Sheet.Range("A9"), Main_Sheet.Range("A9").End(xlDown).End(xlToRight)).Copy
Gangula
  • 5,193
  • 4
  • 30
  • 59