Debug Q

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have the following code that enters Debug at the line that says
"ActiveSheet.Paste". The target sheet i.e. E-Figures is not protected, can
anyone see any reason for this? It states in the dialogue box "Paste method
of worksheet class failed"

Thanks



Sub Generate_Reports()

Sheets("Figures").Select
ActiveSheet.Unprotect Password:="enter"

Cells.Select
Selection.Copy
Range("A1").Select
Sheets("E-Figures").Visible = True
Sheets("E-Figures").Select
ActiveSheet.Unprotect Password:="enter"
Sheets("E-Figures").Select
Range("A1").Select
ActiveSheet.Paste
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Visible = False
Sheets("Home").Select
Range("A1").Select

Mail_Reports
End Sub
 
Not quite sure what you are trying to do but hidden doesn't matter and you
don't have to select. This will from anywhere copy d1 to sheet1!d12

Sub copytoprotectedsheet()
With Sheets("destinationsheet")
.Unprotect
Sheets("sourcesheet").Range("d1").Copy .Range("d12")
.Protect
End With
End Sub

Repost with your successful macro for all to see
 
Thanks Don

What I find when I just select an area is that I get the message along the
lines of "your merged cells are not the same size". So I tend to copy/paste
and then copy / paste special values (as I don't want links or formuls in my
target worksheet). What I'm trying to do is copy all f "Figures" to sheet
"E-Figures", nothing more complicated than that. Sheet E-Figures was
originally hidden
 
try this from anywhere in the workbook. No need to unhide.

Sub copypastetohiddensheet()
With Sheets("destinationsheet")
.Unprotect
Sheets("sourcesheet").Cells.Copy
..Range("a1").PasteSpecial Paste:=xlPasteAll
..Range("a1").PasteSpecial Paste:=xlPasteValues
.Protect
End With
End Sub
 
Don

Using just your code the exact error appearing is "This operation requires
the merged cells to be identiacally sized", the one I paraphrased below,
thats why I tended to copy, then paste / special with the target sheet.
Going in to debug the following line is highlighted
".Range("a1").PasteSpecial Paste:=xlPasteValues"

Thanks
 
Don

Fixed it by 'un merging' a group of 3 cells I had. I've heard there is
problems with copying paste with merged cells but suerly it shouldn't be
like that

Thanks

J
 
Repeat after me: Merged cells are BAAAD.

Center across Selection serves the same purpose in the vast majority of
cases and does NOT affect selection of ranges, copy/paste operations, and
several other things that merging screws up.
 
Back
Top