PasteSpecial problems

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
Hi,

From reviewing past logs, I see that this is a problem that many have had. Unfortunately, I was not able to apply the previous answers to come up with a resolution. I'm getting the Run-time error '1004' - "PasteSpecial method of Range class failed" error when it hits the last line of code below.

Any ideas what is going on?

Thanks very much for any help.

Regards,
T.J.

CODE:

Workbooks.Open Filename:="S:\Secops Finance\KERRILLBERNIE\TJ\SuperCombinedforAnalysis.xls"

Workbooks("DROP DOWN MASTER LIST.xls").Sheets("Activity List").Copy After:=Workbooks("SuperCombinedforAnalysis.xls") _
.Sheets(1)

Workbooks("DROP DOWN MASTER LIST.xls").Sheets("Activity Log").Range("A1062").Copy

'CODE BLOWING-UP HERE
Workbooks("SuperCombinedForAnalysis").Sheets("SuperCombinedForAnalysis").Range("AL2").PasteSpecial Paste:=xlDataValidation, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
I also meant to mention that the copy is still on the clipboard...(i.e., the clipboard is not being cleared by something else).

Thanks,
T.J.
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
OK, I'm still working through this here and found another clue. I ran the code up to the point of the PasteSpecial. I then manually tried to paste special into the cell specified in the code (the copy was still on the clipboard). At that point, I received the following Informational message:

"A formula or sheet you want to move or copy contains the name "ValList", which already exists on the destination worksheet. Do you want to use this version of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box"

My answer to that Yes/No Informational message would always be Yes as ValList is the named range I want in my validation. Trying to figure out how to code around this ... please let me know if you have any ideas as I continue researching.

Thanks,
T.J.[/quote]
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
Still having the problem. The named range issue doesn't appear to be the problem as I deleted the range name from the destination spreadsheet yet the problem persisted.

What I find bizarre is that I can record a macro doing exactly what I want. When I immediately try to run the macro again, it gives me the same error that I've had all along. Could there be some sort of setting that allows me to manually perform a task, yet does not allow it via a macro???? This is quite perplexing!

Thanks,
T.J.
 

Gettingbetter

Well-known Member
Joined
Oct 12, 2004
Messages
602

ADVERTISEMENT

Try putting

Application.DisplayAlerts = False

Before your paste special then put

Application.DisplayAlerts = True

After it

Cheers
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
Thanks for the response. I tried this yesterday...but, was having the same problem.
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
I believe I discovered why the PasteSpecial was not working. Looking at the PasteSpecial Method as outlined by Microsoft (http://msdn.microsoft.com/library/d...us/off2000/html/xlmthpastespecialrangeobj.asp), it looks like "xlDataValidation" does not exist as a "Paste" variant...even though the macro recorder codes it as such!!

I've been tearing my hair out for two days trying to understand why my code wasn't working. Now, that I THINK I may have found the problem...does anyone have any ideas as to how to get around this???

The reason I only want to pastespecial is because I don't want to overwrite any values that exist in the destination cells.

When I changed my code to have a paste variant of "xlPasteFormulas" it worked fine!

NOTE - I'm using Excel 2000.

Thanks,
T.J.
 

tjharlan

Board Regular
Joined
May 8, 2004
Messages
63
I FINALLY GOT IT!!

Man, what a nightmare. Well, I saw that there was a problem posted on microsoft's website in relation to Excel 2000 and PasteSpecial.

However, the problem was very specific that it pertained to the "xlColumnWidths" "Paste" variant. It stated that the following:

"Although Microsoft Excel displays the xlColumnWidths member when you use the Macro Recorder to record a macro, it is not defined in the object model of Excel. Because it is not defined in the object model, it does not appear in the Object Browser in the Visual Basic Editor (on the View menu, click Object Browser)."

Thus, it suggested to change the code from:

Selection.PasteSpecial Paste:=xlColumnWidths

to:

Selection.PasteSpecial Paste:=8

Well, I used the same logic and changed my code to be as follows:

Selection.PasteSpecial Paste:=6 to represent "xlDataValidation" and it worked.

Hope this helps if anyone has had this problem in the future.
 

Forum statistics

Threads
1,147,510
Messages
5,741,582
Members
423,669
Latest member
necat02

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