Run Time Error '1004' PasteSpecial method of Range class failed.

TDPorter

New Member
Joined
Sep 13, 2011
Messages
12
So it worked perfectly!... Until I tried to use it for some additional cells that I wanted to copy from the temp file to the active worksheet "overview". What am I doing wrong? The error I recieved is as follows:

Run Time Error '1004' PasteSpecial method of Range class failed.

See code below:

Dim wbS As Workbook
Dim ws As Worksheet, wsA As Worksheet
Dim myFile As String
Set wsA = ThisWorkbook.Sheets("overview")
myFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Set wbS = Workbooks.Open(myFile, False, True)
Set ws = wbS.Sheets("DAILY TALLY")
ws.Range("D31:S31").Copy
wsA.Range("O40").PasteSpecial Paste:=xlPasteValues, Transpose:=True
ws.Range("U31:W31").Copy
wsA.Range("J48").PasteSpecial Paste:=x1PasteValues, Transpose:=True
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You've actually got x1PasteValues for the 2nd PasteSpecial.
 
Upvote 0
It's only that way in the thread, my macro shows the correct "xlPasteValues", no "1"..... Any help?
 
Last edited:
Upvote 0
That's the only thing that appears wrong.

Is the destination workshet protected at all?

Does it have merged cells?
 
Upvote 0
Nope, not protected and not copying or pasting from any merged cells.... It works fine if I remove the last two lines. Its only when I try to grab the second set of cells it doesn't like it anymore.
 
Upvote 0
Do you have Option Explicit at the top of the code?
 
Upvote 0
Not that I can tell, I'm not sure what that is........ Sorry, still learning, I'm a novice or even beginner in some eyes.
 
Upvote 0
Goto the top of the module and just type Option Explicit.

Then Debug>Compile VBAProject.

Does that highlight, warn about any problems?
 
Upvote 0
That worked! What exactly does "Option Explicit" do when put in the code? Besides make it work....
 
Upvote 0
Well, it shouldn't actually do anything.

Using Option Explicit forces you to declare all variables, which is a good thing.

It helps you eliminate typos for example.

Anyway, after you added it what happened the next time you ran the code?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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