PasteSpecial problems

[tjharlan]

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

 

[tjharlan]

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]

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]

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]

Try putting

Application.DisplayAlerts = False

Before your paste special then put

Application.DisplayAlerts = True

After it

Cheers

 

[tjharlan]

Thanks for the response. I tried this yesterday...but, was having the same problem.

 

[tjharlan]

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]

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.