0

I am trying to write a macro that takes parts of one sheet and paste values on the next. I know using select isn't ideal. But i Don't know how to do it other wise. In the past i have got a out of range error if i was not selecting the sheet before hand. In the macro i have y defined earlier but I am getting an

1004 application-defined or object-defined error

y = Sheets("sheet1").Range("B1", Range("B2").End(xlDown)).Count Sheets("Bucket12").Select Sheets("Bucket12").Range("C2", Range("C2").End(xlDown)).Copy Sheets("upload").Range(Cells(y, 2)).PasteSpecial xlPasteValues Sheets("Bucket12").Range("E2", Range("E2").End(xlDown)).Copy Sheets("upload").Range(Cells(y, 3)).PasteSpecial xlPasteValues Sheets("Bucket12").Range("G2", Range("G2").End(xlDown)).Copy Sheets("upload").Range(Cells(y, 5)).PasteSpecial xlPasteValues Application.CutCopyMode = False
Improve this question
10
  • where are you getting this error? Are you sure there are worksheets (in the same workbook) called Bucket12 and upload? Commented Jul 5, 2018 at 18:38
  • yes i am getting an error on the first line with y in it Commented Jul 5, 2018 at 18:39
  • 1
    @UserX I've fixed your error message for you, but please take note that error messages are very explicit, so it's important that you write (and spell) exactly what they're telling you - otherwise, it's hard to know what your issue is. Commented Jul 5, 2018 at 18:42
  • 2
    Be very careful of references like Cells(y, 3) where you don't specify the parent sheet name. It is implicitly referencing the activesheet. Much of what I can see could be re-written to avoid .Select and use With statements that hold the parent sheet and inside of the With use .Cells or .Range. Commented Jul 5, 2018 at 18:54
  • 2
    The second range in the copy is using whichever sheet is active. If you have Bucket12 selected then Cells(y,3) is using that and not upload. The two ranges are on different sheets so will throw an error. Commented Jul 5, 2018 at 18:55

1 Answer 1

Reset to default
3

The issue is that Range() expects two arguments - Cell1 and Cell2 - you're only giving it one argument, which is throwing error 1004.

Instead, just use .Cells():

y = Sheets("sheet1").Range("B1", Range("B2").End(xlDown)).Count Sheets("Bucket12").Select Sheets("Bucket12").Range("C2", Range("C2").End(xlDown)).Copy Sheets("upload").Cells(y, 2).PasteSpecial xlPasteValues Sheets("Bucket12").Range("E2", Range("E2").End(xlDown)).Copy Sheets("upload").Cells(y, 3).PasteSpecial xlPasteValues Sheets("Bucket12").Range("G2", Range("G2").End(xlDown)).Copy Sheets("upload").Cells(y, 5).PasteSpecial xlPasteValues Application.CutCopyMode = False

Better yet, let's avoid Select, Copy and Paste altogether:

y = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 2).End(xlUp).Row Dim sht1 As Worksheet, sht2 As Worksheet, lastrow As Long Set sht1 = ThisWorkbook.Worksheets("Bucket12") Set sht2 = ThisWorkbook.Worksheets("upload") lastrow = sht1.Cells(sht1.Rows.Count, 3).End(xlUp).Row sht2.Range(sht2.Cells(y, 2), sht2.Cells(lastrow + y - 2, 2)).Value = _ sht1.Range(sht1.Cells(2, 3), sht1.Cells(lastrow, 3)).Value lastrow = sht1.Cells(sht1.Rows.Count, 5).End(xlUp).Row sht2.Range(sht2.Cells(y, 3), sht2.Cells(lastrow + y - 2, 3)).Value = _ sht1.Range(sht1.Cells(2, 5), sht1.Cells(lastrow, 5)).Value lastrow = sht1.Cells(sht1.Rows.Count, 7).End(xlUp).Row sht2.Range(sht2.Cells(y, 5), sht2.Cells(lastrow + y - 2, 5)).Value = _ sht1.Range(sht1.Cells(2, 7), sht1.Cells(lastrow, 7)).Value

As another note - it's better to use xlUp than xlDown when determining your lastrow for data entry.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks. @dwirony Why is it better? So it doesn't go all the way down to the bottom of the sheet by accident?
@UserX Not only that, but blanks in your column will halt the search, giving you a false representation.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.