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

[anna99]

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.

 

[JLGWhiz]

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

 

[anna99]

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