Copy Paste using vba

[rrocker1405]

Hi team,

I'm very sure that I'm doing something wrong in the below code because of which everytime it shows up debugging error stating 'copy method of range class failed'. Any suggestions / advise?

I'm trying to copy the range E4:N4 to E6:N60000 with formulas in the same worksheet.

I tried the following aproaches to replicate formulas but in vain.

sub copy()
Worksheets("sheet1").Range("e4:n4").Copy _
Destination:=Worksheets("sheet1").Range("E6")
'then use autofill to range ("E6:N60000")
end sub

Example 1>
Range("E4:N4").Copy Destination:=Range("E6:N60000")

Example 2>
Sheet9.Range("e4:n4").Copy
Sheet9.Range("e6:n6").PasteSpecial xlPasteFormulas
Application.CutCopyMode = False

Example 3>
Sheets("P_Data").Select
Range("e4:n4").Copy
Sheets("P_Data").Select
Range("e6:n6").PasteSpecial xlPasteFormulas

Thanks in advance.

Kind Regards,
A!

 

[Luke M]

Your syntax looks good. Do you have merged cell in your worksheet by chance? Are any of the worksheets in question protected?

 

[rrocker1405]

Hi Luke,

There are no merged cells and none of the worksheets are protected but for some reason I keep getting error messages. I spent my day exploring various options but didn't help me at all. Not sure what I'm doing in correctly.

Kind regards,
Anand

 

[Luke M]

Could you upload your workbook? I just test the code in a dummy workbook, and it runs perfectly. So, it's not a problem with the code...

 

[Deepak]

Hi rrocker1405,

While uploading what's Luke M asked , don't forget to share screenshot of the debug screen.

 

[rrocker1405]

Hi Luke & Deepak,

I was able to fix this. There was a filter in one of the fields and thats why I kept viewing this error message. After I removed the filter, it worked fine.

Thanks once again as always grateful.

Kind regads,
Anand