PasteSpecial Error

[WaterLover]

I'm copying (Ctl-C) tabular data from a website and wanting to Paste it into my spreadsheet. When I manually select the destination cell and type Ctl-V it works just fine. However, when I try Pasting it via VBA I'm getting the Run-time error 1004 (PasteSpecial method of Range class failed).

My code is simple:

range("A4").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False
​

Also, I had If Application.CutCopyMode = True set as a condition before Pasting and it kept thinking the clipboard was empty - eventhough, Ctl-V continued to work.

I've read about Macros getting in the way of Pasting via VBA but I don't know how to fix this or get around it.

Any ideas?

 

[Dryver14]

Not sure what precedes that line but is it worth trying after copy ......destination:= Range("A4")

 

[Anthony47]

You are using "Range.PasteSpecial" method, that can paste a "Range" from the clipboard to the range. But you don't have copied a Range, but a html document.

You should use "Worksheet.PasteSpecial" method:

Range("A4").Select
ActiveSheet.PasteSpecial NoHTMLFormattinG:=True

Bye

 

[WaterLover]

Thank you. That worked! And, thanks for explaining what was happening.

I also tried SendKeys "^v" and that worked as well.

It's taking a very long time (to the point where Excel says it's not responding) Pasting the information (when it didn't used to). I have Worksheet_Change(ByVal Target As Range) set on another Sheet. Could that be affecting the Pasting on the active sheet?

Thank you again.

You are using "Range.PasteSpecial" method, that can paste a "Range" from the clipboard to the range. But you don't have copied a Range, but a html document.

You should use "Worksheet.PasteSpecial" method:

Range("A4").Select
ActiveSheet.PasteSpecial NoHTMLFormattinG:=True

Bye

 

[Anthony47]

A Worksheet_Change macro works only when changes are inserted in the specific Worksheet; so it should not reduce the speed for copying the information.
And, by the way, I tryed copying complex tables, and it is quite fast.

But anyway try adding Event=disabled and calculation =manual, in this way:

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Range("A4").Select
ActiveSheet.PasteSpecial NoHTMLFormattinG:=True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Also, in this other discussion I suggested the code to import to Excel the tables contained on a web page:
https://www.mrexcel.com/forum/excel...h-webpage-tables-post5037144.html#post5037144

It will not make things faster, but maybe it can help automatizing the whole process.

Bye

 

[WaterLover]

I tried the changes your recommended and they did not work. I'm only Pasting 4 columns and 56 rows of numbers so it's not that much data. Before, it would Paste with no delay but now it's taking about 15 seconds to Paste. The only thing I can think of is the Worksheet_Change event but that's on a different Sheet and is Private so I wouldn't expect it to affect what I'm doing here. But, I don't know Excel very well from a programming point-of-view.

I took a look at your code regarding importing html tables into Excel and I don't know if that'll work because the table isn't the only thing on the webpage. There may be other Tables used for formatting, etc.

Any other thoughts on what might be slowing down the Paste? Could formatting be causing the delay?

Thanks

 

[WaterLover]

I've been playing around with it and it appears that the delay is caused by the Source Formatting. When I Paste it into Word, it Pastes it as a Table and does so quickly. I'm just not sure why it's taking a long time in Excel.

 

[Anthony47]

I've been playing around with it and it appears that the delay is caused by the Source Formatting. When I Paste it into Word, it Pastes it as a Table and does so quickly. I'm just not sure why it's taking a long time in Excel.

A Worksheet_Change macro set in another worksheet doesn't matter; "expecially" after using Application.EnableEvents = False /True.

Does the delay occour also if you paste to a new workbook? Regardless you use Contr-V or ActiveSheet.PasteSpecial NoHTMLFormattinG:=True?

As I said, copying & pasting complex tables I had not the delay problem; can you share the url on wich you copied the table?

Bye

 

[WaterLover]

Yes, there is the same delay if I Ctl-V the values into a new workbook. That's interesting because I don't think it was happening before. So, it must have something to do with the values being Pasted. I can't share the url because it's an industry only website.

But, here is what I just found out: I was using the Microsoft Edge browser and am getting the delay when I paste the values in. However, when using Firefox, there's no delay. Why do you think that is?

Thank you for all your help. It was really helpful.