Excel VBA Macro: Autofilter, Copy, Paste to New Sheet and Save to Specified Location (Dynamic Range)

Excel VBA Macro: Autofilter, Copy, Paste to New Sheet and Save to Specified Location (Dynamic Range). In this video, we write code that allows a user to filter data based on cell value, copy the visible range dynamically, paste to another sheet, and then save the data on the sheet in its own workbook to a specific location. We also go over how to clear data and using ScreenUpdating and DisplayAlerts to prevent random popups and limit excessive visuals while running the macro.

Code:

Sub filter_copy_paste_save()

Dim region As String
Dim raw As Worksheet
Dim out As Worksheet
Dim count_col As Integer
Dim count_row As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set raw = ThisWorkbook.Sheets("Raw Data")
Set out = ThisWorkbook.Sheets("Output")
region = raw.Range("F2").Text

'clear pervious data
out.Cells.ClearContents

'determine the size of the range
raw.Activate
count_col = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown))) + 3

'filter data on Raw Data tab
raw.Range("A4").AutoFilter field:=2, Criteria1:=region

'copy/paste to Output tab
raw.Range(Cells(4, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
out.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False

'show data and remove filter
With raw
.ShowAllData
.AutoFilterMode = False
End With

'formatting Output tab
With out
.Activate
.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("A1").Select
.Copy
End With

'save and close the workbook
ActiveWorkbook.SaveAs Filename:="C:\Users\greggowaffles\Documents\Youtube Videos\Test\" & _
"Region Report - " & region & ".xlsx"
ActiveWorkbook.Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Data used in this video:

#ExcelVBA #ExcelMacro

[YTXYZ]

This was one of the clearest and easiest to follow tutorials on this subject that I’ve seen. Thank you!! I hope I can be successful at making a few minor changes to what data is selected and where it’s saved …. (only copying a few of the rows and columns to another worksheet within the same workbook, and using an input box to allow the user to select that specific range which gets copied). I’ll be checking out your other videos and subscribing. Keep up the nice work!!

[gaybarile]

Thank you very much for this tutorial. I am new to VBA and I find your videos very helpful.

[andrepkownage]

I really need your help, is it possible to edit your marco to be able to save every fliter region information instead of having to click in one by one?

[kashmoney]

Hey - are you able to save the same workbook with the macros? This is amazing - really helpful! I need your guidance where I have a bunch of pivot tables that need to be refreshed before saving the file name, is that possible in your macro at the end? Thanks a lot!!!

[RayRay-yj]

Hey this is great stuff and excellent explained! I do have a questions. How to modify the code to filter rows based on dates?

[KenDC-bv]

Hi Greg, Thanks for this tutorial. And could you help me with the code instead of filtering it one by one is there a possible way to run all the region?

[Lyriks_]

Hello i'm digging your tutorials ! Could you do more on pdf automation ?

[coolguy]

Great Video! One question, how do I make the name of the sheet the same as the name of the file though? For example in your video when you save each file, as you automate the name of that file, is there a similar way to automate the name of the sheet in the bottom left corner of the excel file too?

[rc]

is there a way after you filter on the region to save that to its own tab for each instance w/in the same workbook ? thanks.

[adamsreef]

Hello greg. I need help on how to auto filter, copy and save to new workbook if there is an the additonal worksheet wherein i have there a simple pivot table for summary. So basically i have the first sheet as raw data and the second worksheet as with pivot table summary. Hope you can see this.

[paeng]

I am having problem on the Set raw details
It has debu on it

[EatGyroSandwiches]

Hi. Subscriber here. I’ve been using this macro and it’s working great! My list is growing. How can I get the macro to auto loop down the list so I don’t have to run the macro for each item in the list?