Hi
A Newby here. I've have used the macro recorder to perform certain tasks (see macro code below) but need help adjusting code to make available for a dynamic range as the macro will be used on different data sets. As you can see when I autofilter on the 2nd column it records the number of rows which it will apply again to the next set of data which may contain more rows that need to be processed. So I am trying to make the amount of rows (& possibly columns) dynamic in this operation.
Can anybody please help.
Thank you
Sub usage9macroscombined()
'
' usage9macroscombined Macro
' from start to finish
'
' usage1sort Macro
' open and sort raw usage file before copying to macro-1 template
'
'
ChDir "F:\Work-Macro"
Workbooks.Open Filename:="F:\Work-Macro\usage.xls"
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
End With
Range("D:E,I:L").Select
Range("I1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:F").Select
Range("F1").Activate
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("B1:B124"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
A Newby here. I've have used the macro recorder to perform certain tasks (see macro code below) but need help adjusting code to make available for a dynamic range as the macro will be used on different data sets. As you can see when I autofilter on the 2nd column it records the number of rows which it will apply again to the next set of data which may contain more rows that need to be processed. So I am trying to make the amount of rows (& possibly columns) dynamic in this operation.
Can anybody please help.
Thank you
Sub usage9macroscombined()
'
' usage9macroscombined Macro
' from start to finish
'
' usage1sort Macro
' open and sort raw usage file before copying to macro-1 template
'
'
ChDir "F:\Work-Macro"
Workbooks.Open Filename:="F:\Work-Macro\usage.xls"
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
End With
Range("D:E,I:L").Select
Range("I1").Activate
Selection.Delete Shift:=xlToLeft
Columns("A:F").Select
Range("F1").Activate
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add2 Key:= _
Range("B1:B124"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With