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

Excel VBA Macro: Autofilter And Copy to New Workbook (Dynamic Range). In this video, we go over how to automatically copy and paste filtered data from one workbook to a new workbook. We then save the new workbook based on a cell value that we used to filter the data. We also go over how to handle overwriting data when saving. One way is to use Application.DisplayAlerts = False/True to avoid the issue all together and have the macro always overwrite the existing workbook. The other way is to use On Error Resume Next to handle an error caused when the user selects "No" when asked to overwrite data.

Data used in this video:

#ExcelVBA #ExcelMacro

[karanc]

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

[johnlaymon]

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)))

Field:=1, Criteria1:=organization

'copies data from sheet to workbook
og.Range(Cells(1, 1), Cells(count_row, count_col)). _

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

[Yaktahbay]

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.

[kevinpather]

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? 😥

[weiyu]

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?

[melissaobrien]

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?

[melissagarcia]

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)?

[marcod]

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!

[kennethjensen]

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

[weiyu]

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)))


Field:=6, Criteria1:=tin
'copies data from sheet to workbook
og.Range(Cells(6, 1), Cells(count_row, count_col)). _

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 & _
tin & " " & Format(Date, "mm-dd-yyyy") & ".xlsx"
wb.Close 'savechanges:=False
Application.DisplayAlerts = False
Loop
End Sub

[pankajkumar-riwv]

Unable to understand as code is not visible