i am working with the following sheet and need some help
Excel 2007
and i got the following code from mr excel podcast
http://www.youtube.com/watch?v=K9V5E_LPiKs<code>
Sub LoopThrough()
' this macro is taken from
' http://www.youtube.com/watch?v=K9V5E_LPiKs
Dim wso As Worksheet
Set wso = ActiveSheet
For Each Cell In wso.Range("B1:B5")
ActiveWorkbook.Worksheets.Add
ThisURL = "URL;" & Cell.Value
With ActiveSheet.QueryTables.Add(Connection:= _
ThisURL, Destination:=Range("$B$1"))
.Name = "f-10"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
'xlWebFormattingAll All formatting is imported.
'xlWebFormattingNone No formatting is imported.
'xlWebFormattingRTF Rich Text Format compatible formatting is imported.
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next Cell
End Sub</code>
what i need is to modify the above macro to display info on all the urls (colA) on a single master sheet instead of opening 5 worksheets as per the above code
| Excel Workbook | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | HELP!!!!!!Urgent | http://www.mrexcel.com/forum/showthread.php?t=4994 | ||
| 2 | Vlookup, not worrking as described. | http://www.mrexcel.com/forum/showthread.php?t=4995 | ||
| 3 | HELP Urgent (sugar1525) | http://www.mrexcel.com/forum/showthread.php?t=4996 | ||
| 4 | Vlookup, not working as described. | http://www.mrexcel.com/forum/showthread.php?t=4997 | ||
| 5 | Remove data from a text string in a single cell and place in | http://www.mrexcel.com/forum/showthread.php?t=4804 | ||
| 6 | COMBO BOX Drop List Function | http://www.mrexcel.com/forum/showthread.php?t=4933 | ||
Sheet1 | ||||
and i got the following code from mr excel podcast
http://www.youtube.com/watch?v=K9V5E_LPiKs<code>
Sub LoopThrough()
' this macro is taken from
' http://www.youtube.com/watch?v=K9V5E_LPiKs
Dim wso As Worksheet
Set wso = ActiveSheet
For Each Cell In wso.Range("B1:B5")
ActiveWorkbook.Worksheets.Add
ThisURL = "URL;" & Cell.Value
With ActiveSheet.QueryTables.Add(Connection:= _
ThisURL, Destination:=Range("$B$1"))
.Name = "f-10"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
'xlWebFormattingAll All formatting is imported.
'xlWebFormattingNone No formatting is imported.
'xlWebFormattingRTF Rich Text Format compatible formatting is imported.
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next Cell
End Sub</code>
what i need is to modify the above macro to display info on all the urls (colA) on a single master sheet instead of opening 5 worksheets as per the above code
