Excel VBA Macro: Autofilter And Copy to New Workbook (Dynamic Range) 

How to add extra codes so that we can produce 200 new workbooks if the list has 200 regions using the macro instead we click the drop down list 200 times?

Can this possible to Save Active Sheet As Multiple Workbook like you did in Excel VBA Macro: Save Active Sheet As Multiple PDFs (Based on List of Cell Values)?

Hi - thanks for this. This is what I need but I want to loop through the drop down instead of selecting each one individually. I looked at the other loop for video you posted but that creates PDF. So I basically need a merge of this code and the loop one to auto create all files at once for excel. Then attach that to an email per 'client' (region in your example). I tried to do this myself but got errors. Can you assist please?

Thank you so much for your help! I have been watching your videos and playing with code the past two days and was able to use this and the other code you have for PDF to make a loop for this but export to Excel. The only thing different is I made the data validation list in A1 on sheet 1 and then my column for region was named organization and I changed it to be under Column A(2). Sub copy_data_2_new_book() Dim count_col As Integer Dim count_row As Integer Dim og As Worksheet Dim wb As Workbook Dim organization As String Dim i As Long Set Data = ThisWorkbook.Sheets(1) Set List = ThisWorkbook.Sheets(2) 'count numnber of regions List.Activate Count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown))) Data.Activate For i = 1 To Count 'updating the region name organization = List.Cells(i, 1).Text Data.Cells(1, 1) = organization Set og = Sheet1 organization = og.Cells(1, 1).Value Set wb = Workbooks.Add wb.Sheets("Sheet1").Name = organization og.Activate count_col = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlToRight))) count_row = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlDown))) ActiveSheet.Range("A2").AutoFilter Field:=1, Criteria1:=organization 'copies data from sheet to workbook og.Range(Cells(1, 1), Cells(count_row, count_col)). _ SpecialCells(xlCellTypeVisible).Copy wb.Sheets(organization).Cells(1, 1).PasteSpecial xlPasteValues wb.Sheets(organization).Cells(1, 1).PasteSpecial xlPasteFormats Application.CutCopyMode = False og.ShowAllData og.AutoFilterMode = False wb.Activate Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select 'save and close On Error Resume Next Application.DisplayAlerts = False wb.SaveAs "/Users/To Be Sorted/" & _ organization & " " & Format(Date, "mm-dd-yyyy") & ".xlsx" Application.DisplayAlerts = True wb.Close 'savechanges:=False Next i End Sub

I am getting an error with your code the error occurs at wb.Sheets("Sheet1").Name = region Run-time error 'error': Application-defined or object-defined error Is it possible you can post your excel

Hi Greg, thanks for the video Very clear and I learn a lot and make me more interested with macro. I m trying to do the same thing as your video. That is, taking data filter it according to specific shop and save to its respective files in another folder but I need to the files to be remained in the folder. They should be updated instead and not deleted and replaced by new ones. This is so because the access of each file is shared with a list of sales people and they can access the file on their mobiles. If I do it the same way as you, when they will click on the file, they will not able to access. I am thinking that it will need to create a loop so that each is opened and each one is updated or overwrite and then the each file is closed. Can you please help me with the code as I m not able to make it work? 😥

add the loop , below is the code Dim i As Integer Dim og As Worksheet Dim wb As Workbook Dim tin As String Dim count_col As Integer Dim count_row As Integer i = 1 Do Until i = 2 i = i + 1 Sheets("Template").Select Set og = Sheets("Template") og.Cells(2, 3).Value = i Calculate tin = og.Cells(2, 4).Value Set wb = Workbooks.Add wb.Sheets("Sheet1").Name = tin og.Activate count_col = WorksheetFunction.CountA(Range("A6", Range("A6").End(xlToRight))) count_row = WorksheetFunction.CountA(Range("A6", Range("A6").End(xlDown))) ActiveSheet.Range("A6").AutoFilter Field:=6, Criteria1:=tin 'copies data from sheet to workbook og.Range(Cells(6, 1), Cells(count_row, count_col)). _ SpecialCells(xlCellTypeVisible).Copy wb.Sheets(tin).Cells(1, 1).PasteSpecial xlPasteValues Application.CutCopyMode = False og.ShowAllData og.AutoFilterMode = False wb.Activate Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select 'save and close 'On Error Resume Next Application.DisplayAlerts = False wb.SaveAs "C:\Users\wyu\Documents\Test_2021.12\" & _ tin & " " & Format(Date, "mm-dd-yyyy") & ".xlsx" wb.Close 'savechanges:=False Application.DisplayAlerts = False Loop End Sub

Thanks

Thanks for this, Greg! One enhancement I need is a prompt for the "region" instead of either preselecting it from a dropdown or looping through the entire list. Hopefully that's not too difficult to add.

Unable to understand as code is not visible

Great Video Greg! Helped me automate many weekly reports! Thank you so much!

share the code, please

Could I get the code. thanks

I have a list of regions and would like to run a macro for about 20 regions out of the data set. What code can I add to do this versus individually selecting the regions and clicking run. Thank you for the help this video was great!

very nice .....

Thank you.