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

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?

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" wrote:

> 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?
>
>

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

thanks again

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.


"JMB" wrote:

> 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" wrote:
>
> > 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?
> >
> >