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?

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:
[vba] Intersect(Activesheet.UsedRange.EntireRow, Columns("A:X")).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
[/vba]

[stanleydgrom]

Phantomchase, MikeO,

How about without using "Select".

[VBA]

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

[/VBA]


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