Very Simple Copy and Paste Sheet - Error

S

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
 
J

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

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top