Copying A Range of Cells From one Sheet to Another - Error Application Defined or Object Defined

[Matt]

Trying to copy the contents of Sheet 2 starting at cell A2 to Cell B2
on Sheet 1
Here's what I have:

Worksheets("Sheet 2").Range("A2", _
Cells(Worksheets("Sheet 2").UsedRange.Rows.Count, _
Worksheets("Sheet 2").UsedRange.Columns.Count)).Copy _
Destination:=Worksheets("Sheet 1").Range("B2")

This results in Error '1004' Application Defined or Object Defined.
Anythoughts on how to fix this?

 

[Guest]

Assuming your worksheet names actually have spaces in them like your code
does(ie "Sheet 1", "Sheet 2"), then I think the problem is likely that Cells
is not qualified

Cells(Worksheets("Sheet 2").UsedRange.Rows.Count
s/b Worksheets("Sheet").Cells(Worksheets("Sheet 2").UsedRange.Rows.Count

but could be simplied by using With

With Worksheets("Sheet 2")
.Range("A2", .Cells(.UsedRange.Rows.Count, _
.UsedRange.Columns.Count)).Copy _
Destination:=Worksheets("Sheet 1").Range("B2")
End With

Using Cells(UsedRange.Rows.Count, UsedRange.Columns.Count) could be
problematic. Consider, for example, if you have data only in cells C2:C6 of
sheet 2 the range that is copied becomes A2:A5 (5 rows and 1 column in the
usedrange), not A2:C6. If you know the first row and column on Sheet 2 will
have data, it shouldn't be an issue, but if you want A2 through the last cell
of the worksheet consider (beware of the ng word wrap):

With Worksheets("Sheet 2")
.Range("A2", .Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=Worksheets("Sheet 1").Range("B2")
End With

 

[Matt]

yeah it was the Cells, good call (Can't believe i missed it, had been
trying to debug it for awhile)

thanks again

 

[Guest]

Correction:

Cells(Worksheets("Sheet 2").UsedRange.Rows.Count, Worksheets("Sheet
2").UsedRange.Columns.Count)
s/b
Worksheets("Sheet 2").Cells(Worksheets("Sheet 2").UsedRange.Rows.Count,
Worksheets("Sheet 2").UsedRange.Columns.Count)

Be sure to double check your worksheet names as well.