Pastespecial Macro Error: 1004

Diogo Martins

New Member
Joined
Jun 5, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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 )
VBA Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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.

VBA Code:
Sub Macro4()
'
' Macro4 Macro
'

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

Thanks.
 
Upvote 0
Do you have any protection on the sheet? and is U182 visible? also is it in copy mode when you run the code?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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)?

VBA Code:
Sheets("Daily whereabouts").Range("U182").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,  SkipBlanks:=True
 
Upvote 0
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)?

VBA Code:
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 o_O?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top