runtime error 1004

[verlaine]

Hi,
I have the following macro who now return a runtime error 1004, paste method of worksheet class failed.

I don't know how to change it to make it work. Any help ?

Sub Paste_TOP()

Sheets("Details TOP per month").Select
Cells.Select
Range("C1").Activate
Selection.EntireColumn.Hidden = False 'Unhide all columns

NextRow = Range("C65536").End(xlUp).Row + 1
Cells(NextRow, 1).Select 'select next free row

ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

 

[Legacy 68668]

That means your clipboard is empty...
Unhide the column before you Copy.

 

[Jon von der Heyden]

Have you copied something before you trigger the macro?

If you have then try something like:

Sub Paste_TOP()
With Sheets("Details TOP per month")
    .Cells.EntireColumn.Hidden = False 'Unhide all columns
    NextRow = .Range("C" & Rows.Count).End(xlUp).Row + 1
    .Cells(NextRow, 1).PasteSpecial
End With
Application.CutCopyMode = False
End Sub

 

[Jon von der Heyden]

That means your clipboard is empty...
Unhide the column before you Copy.

The unhide shouldn't empty the clipboard. Not for me at least...

 

[Legacy 68668]

The unhide shouldn't empty the clipboard. Not for me at least...

Maybe, what I meant to say was Copy just before Paste.

 

[verlaine]

Have you copied something before you trigger the macro?

If you have then try something like:

Sub Paste_TOP()
With Sheets("Details TOP per month")
    .Cells.EntireColumn.Hidden = False 'Unhide all columns
    NextRow = .Range("C" & Rows.Count).End(xlUp).Row + 1
    .Cells(NextRow, 1).PasteSpecial
End With
Application.CutCopyMode = False
End Sub

Doesn't work i have the same runtime error..

 

[verlaine]

Maybe, what I meant to say was Copy just before Paste.

Yes i do a manual copy a few rows from another workbook

 

[Jon von der Heyden]

It worked for me on a test workbook. Can you code the copy into your module? Do you have to copy the cells manually?

 

[verlaine]

It worked for me on a test workbook. Can you code the copy into your module? Do you have to copy the cells manually?

Yes the macro is in a module. As the selection criteria change for the copy I do it manually.

When I run the macro from the VBA screen (Alt+F11) it works. When I run the macro using the menu Tools > Macros > Macros > Run I have the runtime error.

Any ideas what the difference ?

 

[Legacy 68668]

try

Sub Paste_TOP()
Dim rng As Range
Set rng = Application.InputBox("select range to copy", type:=8)
rng.Copy Workbooks("YourOtherWorkbook.xls").Sheets("Details TOP per month") _
    .Range("C65536").End(xlUp)(2)
Application.CutCopyMode = False
End Sub

Change YourOtherWorkbook to actual workbook name.<!-- / message --><!-- sig -->