Pastespecial Macro Error: 1004

[Diogo Martins]

Hello Everyone,

This is my first post so apologies if I have done something wrong.

I have 2 spreadsheets , one for Planning and the other one for daily whereabouts. both mirror themselves and have Data validation fields with lists defined, since when I copy from the planning to the daily whereabouts I get the message saying that I already have the list created on the Daily whereabouts spreadsheet, to avoid this annoyance I decided to create macro that allows me to do a paste special - values and and number formats which does query the data validation lists.

This is the code I get once I record the Macro ( I want to be able to past the data anywhere on the spreadsheet since it works with slots )

Sub PasteBackup()
'
' PasteBackup Macro
'

'
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Once I try to run the recorded macro I always get the error : Run-time error '1004' PasteSpecial method of range class failed.

Can you please help me with this issue ?

Thanks.

Diogo Martins

 

[mole999]

1004 indicates the something is missing, so how does your macro know where it is going to paste the data. it should need something to target. the recorded macro would have had select statements

 

[Diogo Martins]

1004 indicates the something is missing, so how does your macro know where it is going to paste the data. it should need something to target. the recorded macro would have had select statements

Hi ,

what I posted is what was generated. I have done another one where I also selected a cell but the result is still the same.

Sub Macro4()
'
' Macro4 Macro
'

'
    Range("U182").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
End Sub

Thanks.

 

[MARK858]

Do you have any protection on the sheet? and is U182 visible? also is it in copy mode when you run the code?

 

[Diogo Martins]

Do you have any protection on the sheet? and is U182 visible?

No protection on the sheet and U182 is visible.

By the way this is so I don't to receive the pop-up box saying "The name 'Jobdesc' already exists. Click Yes to use that version of the name, or click No to rename the version of 'JobDesc' you're moving or copying.

If there is any other way of bypassing this I would be thankful for the help. When copying various slots the constant boxes are very annoying.

Thanks.

 

[MARK858]

also is it in copy mode when you run the code?

You probably missed the above question as it was an edit.

What happens with the code below (make sure that the sheet name exactly matches the tab name)?

Sheets("Daily whereabouts").Range("U182").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,  SkipBlanks:=True

 

[Diogo Martins]

You probably missed the above question as it was an edit.

What happens with the code below (make sure that the sheet name exactly matches the tab name)?

Sheets("Daily whereabouts").Range("U182").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,  SkipBlanks:=True

The spreadsheet is not in copy mode also ,

The problem with declaring a range and sheet is like I mentioned in my first post , I need be able to use this macro and paste anywhere on the spreadsheet , hence the absence of declared range. Also I need to use this Macro on various different sheets . This is a Master sheet with various weekly tabs.

I have done it in the past ( macro with no declared range ) but I lost the code and now for the life of me I can't seem to solve such a simple issue ?

 

[MARK858]

The spreadsheet is not in copy mode also

If it isn't in copy mode then there is nothing to paste hence the error. There is nothing wrong with your PasteSpecial syntax (you should really use ActiveCell rather than selection as you only want the one cell), it is just that there is nothing to paste.