Solved: Loops and linking to Internet Explorer Excel file

[Divad]

Firstly, i have a macro that copies data from 2 worksheets. i do this everyday and the worksheet names are always the same. Rather than writing the macro twice is there a loop or something that i can specify the worksheet names in?




Sheets("Sheet2").Select


Dim lastRow


Range("A65536").Select
Selection.End(xlUp).Select


lastRow = ActiveCell.Row


Range(Cells(2, 1), Cells(lastRow, 17)).Copy


Sheets("Sheet1").Select


Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste


Sheets("Sheet3").Select


Range("A65536").Select
Selection.End(xlUp).Select


lastRow = ActiveCell.Row


Range(Cells(2, 1), Cells(lastRow, 17)).Copy


Sheets("Sheet1").Select


Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste


Secondly, i also open an excel worksheet from a link in Internet Explorer (file name is always the same). Is there a macro that i can write to copy data from it as it opens in Internet Explorer?

Thanks

[Oorang]

Hello, here is a trivial example of the concepts you asked about:
Sub Test()
Const strDlmtr_c As String = " : "
Dim newWB As Excel.Workbook
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim displayRng As Excel.Range
Dim cll As Excel.Range
Dim displayMsg As String
'Getting file off website
Set wb = Excel.Workbooks.Open("http://databases.about.com/library/samples/address.xls", False, True)
Set ws = wb.Worksheets(1)
Set displayRng = ws.Range("A1:F1")
For Each cll In displayRng.Cells
displayMsg = displayMsg & cll.Value & strDlmtr_c & cll.Offset(1, 0).Value & vbLf
Next
Set newWB = Excel.Workbooks.Add
newWB.Worksheets.Add
'Looping through sheets
For Each ws In newWB.Worksheets
ws.Cells(1, 1).Value = displayMsg
ws.Columns(1).ColumnWidth = 100
ws.Columns.AutoFit
ws.Rows.AutoFit
Next
MsgBox displayMsg, vbInformation, "First Line"
End Sub

[Divad]

Oorang

Thanks for that, it opens the workbook in Internet Explorer great.

Many Thanks

Gonna bodge some more code to go with it and it will be the best piece of VBA ever written... by me. :thumb

[Oorang]

rofl Good Luck!