Hi,
Could someone help me out with the following. I would like a macro that when run will allow me to open a folder and select .xls files in this folder and add the sheets in each file to one workbook. (each file only has one sheet but all have different names).
I'd like each sheet to be copied to new workbook as seperate tabs. (if the original tab name could be kept would be great)
I have done many searching but couldnt find a thread that suits. Closest I found was this:
http://www.mrexcel.com/forum/showthread.php?t=298659&highlight=select+file
Any help would be much appreciated.
This code kinda of works but doesnt if the tab in the files is not named sheet1:
Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "C:\MyPath" ' change to suit
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(MyPath & "\*.xls", vbNormal)
If Len(strFilename) = 0 Then Exit Sub
Do Until strFilename = ""
Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
Set wsSrc = wbSrc.Worksheets(1)
wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
wbSrc.Close False
strFilename = Dir()
Loop
wbDst.Worksheets(1).Delete
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Could someone help me out with the following. I would like a macro that when run will allow me to open a folder and select .xls files in this folder and add the sheets in each file to one workbook. (each file only has one sheet but all have different names).
I'd like each sheet to be copied to new workbook as seperate tabs. (if the original tab name could be kept would be great)
I have done many searching but couldnt find a thread that suits. Closest I found was this:
http://www.mrexcel.com/forum/showthread.php?t=298659&highlight=select+file
Any help would be much appreciated.
This code kinda of works but doesnt if the tab in the files is not named sheet1:
Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
MyPath = "C:\MyPath" ' change to suit
Set wbDst = Workbooks.Add(xlWBATWorksheet)
strFilename = Dir(MyPath & "\*.xls", vbNormal)
If Len(strFilename) = 0 Then Exit Sub
Do Until strFilename = ""
Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
Set wsSrc = wbSrc.Worksheets(1)
wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
wbSrc.Close False
strFilename = Dir()
Loop
wbDst.Worksheets(1).Delete
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub