Solved: Painfully slow Copy & Paste from Web onto a sheet.

[Phantomchase]

Hi guys

I hope you can help me with a odd problem.
The following code is running smooth in Excel 2003, but in Excel 2007 it keeps hanging when I reach the first Activesheet.paste.


The time for the system to paste the copied data takes me around 5 minutes.

The other odd thing is that when I run it the first time in the morning it runs quickly, but when I run it a second time. The system hangs.

So, looking at the code, is there anything I?m missing here? What can I do to make this more efficient or better?:dunno

I can?t use webquery on the page I?m accessing, so that?s why I keep working with the Copy & Paste method.

I have been scanning the web & forums for clues and answers but with no luck so far:(
I really hope you guys can help me out, I?m getting really frustrated.




Sub update()

Application.ScreenUpdating = False

Sheets("R?data").Visible = True

Set myData = New DataObject

Dim myIE As Object
Set myIE = New InternetExplorer

Dim myURL As String
myURL = "webpage" Can?t post links at the moment.

myIE.navigate myURL

Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop

myData.SetText myIE.Document.body.innerHTML
myData.PutInClipboard

myIE.Quit

Windows("test1.xlsm").Activate
Sheets("R?data").Select
Range("A1").Select
ActiveSheet.Paste

Columns("A:X").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select

Application.CutCopyMode = False

Application.ScreenUpdating = True

End sub




Thanks in advance.

/Phantomchase

[MikeO]

One thing that I notice is that you're copying several entire columns of data. That will result in a significantly larger number of cells being copied using the 2007 version...which would slow it down. Try this instead:
Intersect(Activesheet.UsedRange.EntireRow, Columns("A:X")).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

[stanleydgrom]

Phantomchase, MikeO,

How about without using "Select".



With Intersect(ActiveSheet.UsedRange.EntireRow, Columns("A:X"))
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With




Have a great day,
Stan

[Phantomchase]

Hi guys.

Thanks for the help. I?ll will try it and see how it works.
I?ll be back.

/Phantomchase

[Phantomchase]

Yeeehaaa,,, It works just perfect!.
Thanks both of you!, great to get some assistance when you really need it!

Have a great day!
/Phantomchase