CODEWITHSUNDEEP
vbaexcel
UserX
UserX

Reputation: 159

Copy Paste VBA Issue

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

Upvotes: 0

Views: 69

Answers (1)

dwirony
dwirony

Reputation: 5450

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.

Upvotes: 3

Related Questions