Hi guys,
I have some successful code which takes my active workbook, splits and saves out the sheets.
I would like to now have this code run on a whole folder of excel files in a folder, not the activeworkbook.
Can anyone help me modify it to do this (don't mind if you have to specify the directory with an address or via a filedialog) or can anyone help point me to some reading for work it out myself?
I have some successful code which takes my active workbook, splits and saves out the sheets.
I would like to now have this code run on a whole folder of excel files in a folder, not the activeworkbook.
Can anyone help me modify it to do this (don't mind if you have to specify the directory with an address or via a filedialog) or can anyone help point me to some reading for work it out myself?
Code:
Sub Splitbook()
Dim xPath As String
Dim Path As String
strTestString = Left(Application.ActiveWorkbook.Name, (InStrRev(Application.ActiveWorkbook.Name, ".", -1, vbTestCompare) - 1))
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & "_" & strTestString & ".csv", FileFormat:=xlCSV
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub