How to split 1 master worksheet into multiple excel workbooks (files)? VBA

anna99

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
Hi All, could you please advise. I have a master list of all items on 1 worksheet. I would like to split this worksheet into multiple excel WORKBOOKS (files) based on same value I have in Column A.
So if values in Column A are the same, will create a new workbook for these including title lines...
Is there anyway we can do with VBA? or Power Query?

Thank you.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Sub t()
Dim wb As Workbook, c As Range
With ActiveSheet
    Intersect(.Columns(1), .UsedRange).AdvancedFilter xlFilterCopy, , .Cells(Rows.Count, 2).End(xlUp)(3), True
    For Each c In .Cells(Rows.Count, 2).End(xlUp).CurrentRegion.Offset(1)
        If c <> "" Then
            Set wb = Workbooks.Add
            .UsedRange.AutoFilter 1, c.Value
            .UsedRange.SpecialCells(xlCellTypeVisible).Copy
            wb.Sheets(1).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
            wb.SaveAs ThisWorkbook.Path & "\" & c.Value & ".xlsx", 51
            .AutoFilterMode = False
            wb.Close False
        End If
    Next
    .Cells(Rows.Count, 2).End(xlUp).CurrentRegion.ClearContents
End With
End Sub
 
Solution

anna99

New Member
Joined
Jan 8, 2021
Messages
17
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
  3. Web
VBA Code:
Sub t()
Dim wb As Workbook, c As Range
With ActiveSheet
    Intersect(.Columns(1), .UsedRange).AdvancedFilter xlFilterCopy, , .Cells(Rows.Count, 2).End(xlUp)(3), True
    For Each c In .Cells(Rows.Count, 2).End(xlUp).CurrentRegion.Offset(1)
        If c <> "" Then
            Set wb = Workbooks.Add
            .UsedRange.AutoFilter 1, c.Value
            .UsedRange.SpecialCells(xlCellTypeVisible).Copy
            wb.Sheets(1).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
            wb.SaveAs ThisWorkbook.Path & "\" & c.Value & ".xlsx", 51
            .AutoFilterMode = False
            wb.Close False
        End If
    Next
    .Cells(Rows.Count, 2).End(xlUp).CurrentRegion.ClearContents
End With
End Sub
Thank you for the quote, can you advise the code how can I protect all the new workbooks that just created from editing, but still allow filtering
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top