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

свернуть