VBA to Loop through and Copy Tables from HTML on Websites

[PShingadia]

Hi All:

Really struggling with this and any help would be appreciated!

The following code works at copying a single table from a website and pasting it directly to worksheet. How would you adapt this to copy several tables from a website in a loop with each table on a new sheet?


Option Explicit


' Add reference to Microsoft Internet Controls
' Add reference to Microsoft HTML Object Library
' Add reference to Microsoft Forms 2.0 Object Library


Private Const url As String = "http://www.livecharts.co.uk/share_prices/top_uk_shares.php"


Sub CopyClipboard()
Dim ie As SHDocVw.InternetExplorer
Dim doc As MSHTML.HTMLDocument
Dim tables As MSHTML.IHTMLElementCollection
Dim table As MSHTML.HTMLTable
Dim clipboard As MSForms.DataObject


Set ie = New SHDocVw.InternetExplorer


With ie
.Visible = True
.Navigate url


While .Busy Or .ReadyState <> READYSTATE_COMPLETE
DoEvents
Wend


Set doc = .Document
Set tables = doc.getElementsByTagName("table")
Set table = tables(0)
Set clipboard = New MSForms.DataObject


clipboard.SetText table.outerHTML
clipboard.PutInClipboard
ActiveSheet.Paste


.Quit
End With
End Sub