Very Simple Copy and Paste Sheet

Very Simple Copy and Paste Sheet - Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sean - 23 Sep 2007 13:19 GMT

I have a very simple piece of code (extract below), that copies the
contents from SheetA to SheetB, but I am getting a debug of "Copy
method of Range Class failed" on the line - Slection.Copy below

I can't see how my code could be any simpler and hence why the error

Hope someone can assist

Sub Report()

Application.ScreenUpdating = False
ActiveWindow.DisplayWorkbookTabs = False

Sheets("SheetA").Activate
ActiveSheet.Unprotect Password:="123"
Range("A1").Select
Sheets("SheetB").Visible = True
Sheets("SheetB").Activate
ActiveSheet.Unprotect Password:="123"

Sheets("SheetA").Activate
Cells.Select
Selection.Copy

Reply to this Message

JLGWhiz - 23 Sep 2007 14:48 GMT

It could be that VBA is confused by the Cells.Select since that
equates to the entire sheet. It is expecting a destination of
either After:= or a new workbook. Here is a modified version
of your code. See if it will work.

Application.ScreenUpdating = False
ActiveWindow.DisplayWorkbookTabs = False

Sheets("SheetA").Unprotect Password:="123"
Sheets("SheetB").Visible = True
Sheets("SheetB").Unprotect Password:="123"

Sheets("SheetA").Copy After:=Sheets(Sheets.Count)

> I have a very simple piece of code (extract below), that copies the
> contents from SheetA to SheetB, but I am getting a debug of "Copy
[quoted text clipped - 19 lines]
> Cells.Select
> Selection.Copy

Reply to this Message

Sean - 23 Sep 2007 15:00 GMT

> It could be that VBA is confused by the Cells.Select since that
> equates to the entire sheet. It is expecting a destination of
[quoted text clipped - 35 lines]
>
> - Show quoted text -

Thanks JLGWhiz

What exactly does the line "Sheets("SheetA").Copy
After:=Sheets(Sheets.Count)" mean? Is it copy SheetA to the sheet just
after it i.e the first to the right?

If so won't work for me as the destination sheet is not immediately to
the right (I used Sheet names A & B just to keep it simple)

Reply to this Message

JLGWhiz - 23 Sep 2007 18:52 GMT

If you do not intend to copy the entire sheet, then maybe you would want to use
Sheets("SheetA").UsedRange.Copy instead of Sheets("SheetA").Cells.Copy. That
is less likely to cause the copy error you were getting.

> > It could be that VBA is confused by the Cells.Select since that
> > equates to the entire sheet. It is expecting a destination of
[quoted text clipped - 44 lines]
> If so won't work for me as the destination sheet is not immediately to
> the right (I used Sheet names A & B just to keep it simple)

Reply to this Message