VBA - paste special "error 1004"

[VBA]

Heya,

I'm trying to incoperate "paste special" in my VBA-code and used "macro recorder" to figure out how to do so. However, when I try to run the so developed code it gives an error I have no clue why

CODE:
Sub Ooops()
Sheets("JD").Visible = False
Sheets("Jn").Delete
Sheets("JD").Range("A1:Z480").Copy
Sheets.Add.Name = "Jn"
Sheets("Jn").Range("A10").Select
ActiveSheet.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub

Anybody who can tell me what I'm doing wrong? I can't use "paste" as then I'll get the wrong values...

Thanks in advance

 

[Domski]

Hi & welcome to the board,

Try:

Sub Ooops()
Sheets("JD").Visible = False
Sheets("Jn").Delete
Sheets("JD").Range("A1:Z480").Copy
Sheets.Add.Name = "Jn"
Sheets("Jn").Range("A10").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End Sub

Dom

 

[VBA]

It works

Not that I really understand why I mean, it should be the same... Right?

Ow well, I guess VBA will always stay a bit mysterious Somehow it never quite does what I expect

Thanks a lot!

 

[Domski]

If you have a look at the VBA help it explains the different approaches. The one you were using is applicable to Excel's worksheet object and is for when you want to paste data from another application into Excel however you needed it to be used as applicable to the Range object where you must specify the specific destination range as part of the pastespecial operation.

Dom

 

[VBA]

Thx