divide sheet into books based on column date

[rjmdc]

hi
i have a master sheet with 4 columns, name, date, amount, type
i want to split into separate workbooks based on column date
each workbook with 1 sheet
workbook named as the split month ex. 7_2022, 8_2022
what would be code to do that?
thanks in advance

 

[SunnyAlv]

Try this Example, copy table below

Range F1 = Link Document

Name	Date	Amount	Type		C:\Users\BJ900265\Documents\
AB	11/11/2023​	1	0001
AB	12/10/2023​	2	0002
AC	10/9/2023​	3	0003
AA	12/11/2023​	4	0004
AC	12/7/2023​	5	0005
AA	10/9/2023​	6	0006
AD	10/9/2023​	7	0007
AF	11/11/2023​	8	0008
AH	11/11/2023​	9	0009
AS	11/11/2023​	10	0010

Sub CreateLoop()


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim S1 As Worksheet
Set S1 = ThisWorkbook.Sheets("Sheet2") 'Change With ur Sheet Name

If S1.AutoFilterMode Then S1.AutoFilter.ShowAllData


Dim Nwb, Awb As Workbook
Dim Nsh As Worksheet
Set Awb = ActiveWorkbook


'To Make File Name, u can edit it
Range("F2").Formula = "=TEXTJOIN(""_"",,TEXT(B2,""MM""),TEXT(B2,""YYYY""))"
Range("F2").AutoFill Destination:=Range("F2:F" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select


'Loop
Dim i, f As Integer

For i = 2 To Application.CountA(S1.Range("B:B"))


S1.Range("A1:D1").AutoFilter 2, S1.Range("B" & i).Value 'looping by filter


Set Nwb = Workbooks.Add
Set Nsh = Nwb.Sheets(1)


S1.Range("A1:D1" & Cells(Rows.Count, "A").End(xlUp).Row).Copy  'copy data

Nsh.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Nsh.UsedRange.EntireColumn.ColumnWidth = 15

Nwb.SaveAs S1.Range("F1").Value & S1.Range("F" & i).Value & ".xlsx"
Nwb.Close False


Next i


If S1.AutoFilterMode Then S1.AutoFilter.ShowAllData

S1.Range("F2:F" & Cells(Rows.Count, "B").End(xlUp).Row).ClearContents


MsgBox "Done"


End Sub

*If there’s any unclear information please let me know

 

[rjmdc]

hi
where do i add that each book the sheet needs to be named Sheet1
each table needs to be named Billing_2

 

[SunnyAlv]

where do i add that each book the sheet needs to be named Sheet1

When adding a new Excel book, each sheet will be named "Sheet1" automatically, right?

each table needs to be named Billing_2

what does it mean by naming it "Table"?
What is meant by "table", I don't understand that, sorry

 

[rjmdc]

also sheet needs to be Sheet1