I need help cleaning up and repeating some VBA.
The below code opens up one of my users spreadsheets, extracts the "Customer" data, sorts the remaining lines, then closes and saves the users spreadheet before sorting and cleaning the master spreadsheets.
I am looking for help with two things.
A) cleaning up this code, this is 90% a recorded macro and I am sure it can be condensed
B) Repeating this procedure with all of the files in the folder.
Any help will be appreciated.
Regards.
The below code opens up one of my users spreadsheets, extracts the "Customer" data, sorts the remaining lines, then closes and saves the users spreadheet before sorting and cleaning the master spreadsheets.
I am looking for help with two things.
A) cleaning up this code, this is 90% a recorded macro and I am sure it can be condensed
B) Repeating this procedure with all of the files in the folder.
Any help will be appreciated.
Regards.
Code:
Sub Macro2()
'
' Macro2 Macro
'
'Find first empty row
Range("A1").End(xlDown).Offset(1, 0).Select
'Import Data
'Open User Form
Workbooks.Open Filename:= _
"C:\FD User Entry Forms\FDEntryU1AM.xlsm"
'Open the rows grouping
ActiveSheet.Outline.ShowLevels RowLevels:=2
'Filter to Customer
ActiveSheet.Range("$A$2:$Q$400").AutoFilter Field:=15, Criteria1:="Customer"
'Copy all remaining rows
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
'Paste into Master File
Windows("Customer Bin Master.xlsm").Activate
ActiveSheet.Paste
'Clear data
Windows("FDEntryU1AM.xlsm").Activate
Application.CutCopyMode = False
Selection.ClearContents
'Remove Filtering
ActiveSheet.Range("$A$2:$Q$400").AutoFilter Field:=15
'Sort remaining data to remove blank rows
ActiveWorkbook.Worksheets("Bin Entry").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bin Entry").AutoFilter.Sort.SortFields.Add Key:= _
Range("A2:A400"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Bin Entry").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Close grouping of rows
ActiveSheet.Outline.ShowLevels RowLevels:=1
'Close User File
ActiveWindow.Close SaveChanges:=True
Selection.End(xlDown).Select
'End Data Import
'Sort Master File
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:Q400").Select
ActiveWorkbook.Worksheets("Customer Vendor Bin").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Customer Vendor Bin").Sort.SortFields.Add Key:= _
Range("A2:A400"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Customer Vendor Bin").Sort.SortFields.Add Key:= _
Range("C2:C400"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("Customer Vendor Bin").Sort.SortFields.Add Key:= _
Range("E2:E247"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Customer Vendor Bin").Sort
'Range of entire spreadsheet
.SetRange Range("A1:Q2000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub