Very Simple Copy and Paste Sheet - Error

[Sean]

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

 

[JLGWhiz]

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)

 

[Sean]

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)











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

 

[JLGWhiz]

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.