Last active
February 18, 2021 13:49
-
-
Save SimonXIX/d255f44d2889e960a678e87dc7e187e0 to your computer and use it in GitHub Desktop.
Excel macro to filter a column and save filtered data to a new Excel file for each unique value
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Option Explicit | |
Sub filter() | |
Application.ScreenUpdating = False | |
Dim x As Range | |
Dim rng As Range | |
Dim rng1 As Range | |
Dim last As Long | |
Dim sht As String | |
Dim newBook As Excel.Workbook | |
Dim Workbk As Excel.Workbook | |
'specify sheet name in which the data is stored | |
sht = "download_stat_20210218" | |
'workbook where VBA code resides | |
Set Workbk = ThisWorkbook | |
'change filter column in the following code currently pointing at column J | |
last = Workbk.Sheets(sht).Cells(Rows.Count, "J").End(xlUp).Row | |
With Workbk.Sheets(sht) | |
Set rng = .Range("A1:J" & last) | |
End With | |
Workbk.Sheets(sht).Range("J1:J" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=True | |
'loop through unique values in column | |
For Each x In Workbk.Sheets(sht).Range([AA2], Cells(Rows.Count, "AA").End(xlUp)) | |
With rng | |
.AutoFilter | |
.AutoFilter Field:=10, Criteria1:=x.Value | |
.SpecialCells(xlCellTypeVisible).Copy | |
'add new workbook in loop | |
Set newBook = Workbooks.Add(xlWBATWorksheet) | |
newBook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value | |
newBook.Activate | |
ActiveSheet.Paste | |
End With | |
'save new workbook | |
newBook.SaveAs "download_stat_MPI" & x.Value & ".xlsx" | |
'close workbook | |
newBook.Close SaveChanges:=False | |
Next x | |
'turn off filter | |
Workbk.Sheets(sht).AutoFilterMode = False | |
With Application | |
.CutCopyMode = False | |
.ScreenUpdating = True | |
End With | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment