Help with VBA Code Split Worksheets

lisaming

New Member
Joined
Nov 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I have a working VBA code however, I am trying to change it for "ThisWorkBook" to where I can choose any file and run the macro to split the worksheets into separate files.

Pull this from another macro:
Private Sub Workbook_Open()
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=False)

Existing Code:
Sub SplitEachWorksheet()
Dim FPath As String
FPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Sheets
ws.Copy
Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,075
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
VBA Code:
Sub SplitEachWorksheet()
Dim wb As Workbook
Dim fnameList As String
Dim FPath As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel file to split", MultiSelect:=False)

    Set wb = Workbooks.Open(fnameList
    FPath = wb.Path

    For Each ws In wb.Sheets
        ws.Copy
        Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
 

lisaming

New Member
Joined
Nov 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this.
VBA Code:
Sub SplitEachWorksheet()
Dim wb As Workbook
Dim fnameList As String
Dim FPath As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel file to split", MultiSelect:=False)

    Set wb = Workbooks.Open(fnameList
    FPath = wb.Path

    For Each ws In wb.Sheets
        ws.Copy
        Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx"
        Application.ActiveWorkbook.Close False
    Next

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Thanks for the feedback, however I tried it and it didnt work. How do i incorporate the " Private Sub Workbook_Open() " code into the current one so that I can select the file that needs to be split?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,075
Office Version
  1. 365
Platform
  1. Windows
How did it not work?

Did yo fix the typo?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,763
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

How do i incorporate the " Private Sub Workbook_Open() " code into the current one so that I can select the file that needs to be split?

This is a confusing question, because it does not coincide with this statement.
I am trying to change it for "ThisWorkBook" to where I can choose any file and run the macro to split the worksheets into separate files.

Can you be a little more explicit in what you want to do. Pretend you have no code at all and then explain what you want.
 

lisaming

New Member
Joined
Nov 7, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
This is a confusing question, because it does not coincide with this statement.


Can you be a little more explicit in what you want to do. Pretend you have no code at all and then explain what you want.
Sure thing, I need a macro that will split any workbook (with multiple worksheets) into separate files. The macro will need to prompt the User to select the file to split; and save the separate worksheets in the same location as where the original file was selected from.

Hope this helps clarify.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,763
Office Version
  1. 2013
Platform
  1. Windows
After adding the right parenthesis to the Workbooks.Open statement, @Norie code ran without error and produced desired results for me. I ran the code from code module1 of my workbook. It opens the GetFilename dialo9g box, where I selected a file name and clicked 'Open'. The code then opened the selected workbook, copied the sheets to produce new workbooks, saving each one to the original directory and closing them in turn. What parrt of this process is not working for you.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,897
Members
412,689
Latest member
nhsmedic
Top