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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

Diogo Martins

New Member
Joined
Jun 5, 2020
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Diogo Martins

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,120
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,582
Members
418,401
Latest member
B_A_M155

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
Top