Excel VBA Macro: Autofilter, Copy, Paste to New Sheet and Save to Specified Folder (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: https://gsociology.icaap.org/dataupload.html #ExcelVBA #ExcelMacro