Check this:
Public Sub SUM()
Dim wsh As Worksheet
Dim mystr As String
Dim qt As QueryTable
Dim x As Integer
On Error Resume Next
For x = 1 To 3
mystr = "URL;http://www.medifee.com/hospital/9144-kkasturi-medicare-pvt-ltd/"
Set wsh = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
wsh.Name = x
Set qt = wsh.QueryTables(x)
If Not qt Is Nothing Then wsh.QueryTables(x).Delete
With wsh.QueryTables.Add(Connection:=mystr, Destination:=Range("$B$1"))
.Name = x
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next x
End Sub
I tested that and it works well. Please, read my comment inside code block.
Result:
Hospital Facility Rates
General Ward Rs. 1800.00 (Daily Expenses)
General Ward Rs. 800.00
Semi Private Ward Rs. 2300.00 (Twin sharing-Daily Expenses)
Semi Private Ward Rs. 1200.00
Private Ward Rs. 2800.00 (Daily Expenses)
Private Ward Rs. 1500.00
X-Ray Rs. 350.00 (Chest)
Sonography Rs. 1200.00 (Whole Abdomen)
Sonography Rs. 1000.00 (Upper / Lower Abdomen)
Vitamin D Test Rs. 1400.00
Vitamin B12 Test Rs. 1000.00
Blood Sugar Test Rs. 50.00
Blood Group Test Rs. 100.00
Lipid Profile Rs. 600.00
Thyroid Test Rs. 550.00
Urine Routine Rs. 70.00
Urine Routine Rs. 70.00
Stool Routine Rs. 120.00
Folic Acid Test Rs. 1000.00
HIV Test Rs. 350.00
Iron Test Rs. 700.00 (Profile)
ICU Rs. 2000.00
ICU Rs. 5000.00 (Daily Expenses)
ICU Rs. 600.00 (Monitor charges)
ICU Rs. 2000.00 (Bipep )
Amylase Test Rs. 450.00
APTT (Activated Partial Thromboplastin Time) Test Rs. 350.00
HbA1C Test Rs. 1250.00
...
and so ...