Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through all workbooks
Try somethin like this: (un tested) Public Sub GetData() Dim MyDir As String Dim MyFType As String Dim MyWbook As String Dim MyTWbook As String Dim MyOpenWbook As Workbook Dim MySheet As Worksheet Dim MyCell As Range MyTBook = "C:\My Directory\My Files\My Master file.xls" MyTBook.Open MyDir = "C:\My Directory\My Files" MyFType = "*.XLS" MyWbook = Dir(MyDir & "\" & MyFType) Do Set MyOpenWbook = Workbooks.Open(MyDir & "\" & MyWbook) For Each MySheet In MyOpenWbook If MySheet.Visible = True Then MySheet.Range("A1").Copy MyTBook.Activate Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste End If Next MySheet MyOpenWbook.Save MyOpenWbook.Close MyWbook = Dir() Loop Until MyWbook = "" End Sub Abdul Salam -----Original Message----- Can someone give me a start to the code to open all the workbooks in turn that are in a specific directory and then to copy specific cells to a masterfile in a different directory? thanks in advance . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through all workbooks
Many thanks - about test
Mervyn "Abdul Salam" wrote in message ... Try somethin like this: (un tested) Public Sub GetData() Dim MyDir As String Dim MyFType As String Dim MyWbook As String Dim MyTWbook As String Dim MyOpenWbook As Workbook Dim MySheet As Worksheet Dim MyCell As Range MyTBook = "C:\My Directory\My Files\My Master file.xls" MyTBook.Open MyDir = "C:\My Directory\My Files" MyFType = "*.XLS" MyWbook = Dir(MyDir & "\" & MyFType) Do Set MyOpenWbook = Workbooks.Open(MyDir & "\" & MyWbook) For Each MySheet In MyOpenWbook If MySheet.Visible = True Then MySheet.Range("A1").Copy MyTBook.Activate Range("A65536").End(xlUp).Offset(1, 0).Select ActiveSheet.Paste End If Next MySheet MyOpenWbook.Save MyOpenWbook.Close MyWbook = Dir() Loop Until MyWbook = "" End Sub Abdul Salam -----Original Message----- Can someone give me a start to the code to open all the workbooks in turn that are in a specific directory and then to copy specific cells to a masterfile in a different directory? thanks in advance . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
Looping through 2 workbooks | Excel Discussion (Misc queries) | |||
Not Looping | Excel Discussion (Misc queries) | |||
Looping | Excel Discussion (Misc queries) | |||
VBA code for looping through open workbooks and worksheets | Excel Programming |