Hi - I have a data file that contains the following columns: Name, Region Name, Territory Name, Address, City, State, Zip
I would like to create a macro that would break this data down into separate workbooks by region. There are 9 different regions. I would also like these 9 individual region files to split into multiple worksheets based on the amount of territories per region (i.e. the west region has 10 territories, so it would have 10 separate worksheets of data for the west region file).
Attached is the file i have created so far. I have created the macro to split the files into regions and save them to a specific path on my desktop, however, i need the next step of breaking them into multiple worksheets per region file.
I am looking to continue the code already written, however, I am open to any new code if its easier for the process. Any help would be appreciated! Thanks!
Below is the code i have so far which is in the attached file.
Sub Split_Data_in_workbooks()
Application.ScreenUpdating = False
Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("Data")
Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("Settings")
Dim nwb As Workbook
Dim nsh As Worksheet
''''' Get unique Region
setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("B:B").Copy setting_Sh.Range("A1")
setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes
Dim i As Integer
For i = 2 To Application.CountA(setting_Sh.Range("A:A"))
data_sh.UsedRange.AutoFilter 2, setting_Sh.Range("A" & i).Value
Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")
nsh.UsedRange.EntireColumn.ColumnWidth = 15
nwb.SaveAs setting_Sh.Range("H6").Value & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
nwb.Close False
data_sh.AutoFilterMode = False
Next i
setting_Sh.Range("A:A").Clear
MsgBox "Done"
End Sub
Bookmarks