vrussell
07-14-2006, 10:00 AM
Please help!
How do I write this path? I have an Excel Workbook with 12 worksheets. I would like to combine all 12 worksheets into one workbook. The code used by a friend is:
Option Explicit
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
use vba tags when posting to make your code more readable. I have added them to this post for you...just select your code when posting and hit the button marked vba. Hope thats ok.
However, I can't get the path to work. Here's my path that needs to be rewritten for the macro to work.
D:\My Documents\Word\2006 Georgia Outshopping\Practice and the only file there is the Excel file I want to change called OutshoppingReg01.xls
Thank you for your help!!!!
Vicki
How do I write this path? I have an Excel Workbook with 12 worksheets. I would like to combine all 12 worksheets into one workbook. The code used by a friend is:
Option Explicit
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
use vba tags when posting to make your code more readable. I have added them to this post for you...just select your code when posting and hit the button marked vba. Hope thats ok.
However, I can't get the path to work. Here's my path that needs to be rewritten for the macro to work.
D:\My Documents\Word\2006 Georgia Outshopping\Practice and the only file there is the Excel file I want to change called OutshoppingReg01.xls
Thank you for your help!!!!
Vicki