PDA

View Full Version : Pull data to new workbook with new sheet per workbook



minger15
06-11-2010, 11:27 AM
I have multiple workbooks (all the same with different data) and wish to have a workbook that will pull certain data from them and create a new sheet in this new workbook for each one of the multiple workbooks with the respective data. HELP!!!!!!!!!!!!! I have no clue where to start:dunno

Bob Phillips
06-11-2010, 11:58 AM
Dim oFSO As Object

Sub LoopFolders()
Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing
End Sub

'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

With ThisWorkbook

For Each file In Folder.Files

If file.Type Like "Microsoft*Excel*Worksheet*" Then

Workbooks.Open Filename:=file.Path
ActiveWorkbook.Worksheets("Sheet1").Copy After:=.Worksheets(.Worksheets.Count)
ActiveWorkbook.Close savechanges:=False
End If
End If
Next file
End Sub

GTO
06-11-2010, 12:07 PM
Hi Bob,

Why the Like? A 2007 thing?

Mark

Bob Phillips
06-11-2010, 12:14 PM
Not only 2007, it is also an OS thing as to how they identify workbooks.

GTO
06-11-2010, 12:52 PM
Ahh, I did not think of Mac - thank you :-)

If we do not 'chat', a great weekend to you and yours,

Mark