With all of the methods available to scrub external data, many users forget that you can open a web page full of tables with nothing more than a valid URL and File ► Open. I'm posting the code here but I will also supply a link to a working sample workbook that took ~ 2 minutes to gather the full web page data from 14 sequentially numbered WSN (web serial number) pages. Your own results may vary.

Option Explicit

Public Const csURL As String = "http://sonlite.dnr.state.la.us/sundown/cart_prod/cart_con_wellinfo2?p_WSN=×WSN×"

Sub Gather_Well_Data()
    Dim rw As Long, lr As Long, w As Long, wsn As String, wb As Workbook
    On Error GoTo Fìn
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    With ThisWorkbook.Sheets("WSNs")
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
        For rw = 2 To lr
            .Cells(rw, 2) = 0
            For w = 1 To .Parent.Sheets.Count
                If .Parent.Sheets(w).Name = CStr(.Cells(rw, 1).Value) Then
                    .Parent.Sheets(w).Delete
                    Exit For
                End If
            Next w
            wsn = Replace(csURL, "×WSN×", .Cells(rw, 1).Value)
            Set wb = Workbooks.Open(Filename:=wsn, ReadOnly:=True, addtomru:=False)
            wb.Sheets(1).Range("A1:A3").Font.Size = 12
            wb.Sheets(1).Copy After:=.Parent.Sheets(.Parent.Sheets.Count)
            .Parent.Sheets(.Parent.Sheets.Count).Name = .Cells(rw, 1).Value
            wb.Close savechanges:=False
            Set wb = Nothing
            .Cells(rw, 2) = 1
            Application.ScreenUpdating = True
            Application.ScreenUpdating = False
            .Parent.Save
        Next rw
        .Activate
    End With
Fìn:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub


The list of WSN identifiers are in the WSNs worksheet starting in column 2. Run the macro by tapping Alt+F8 to open the Macros dialog and Run the Gather_Well_Data macro. When it is complete, you will have a workbook filled with worksheets identified by the WSNs similar to below.

         

The sample workbook is on my public DropBox at:

LA_WSN_Data.xlsb

Macro that follows a link and downloads the table into a new sheet
Is it okay to vacuum a laptop keyboard? Would it cause any damage?
