TheRedCardinal
Board Regular
- Joined
- Jul 11, 2019
- Messages
- 243
- Office Version
- 365
- 2021
- Platform
- Windows
In my workbook I have several sheets that contain tables in the same format.
I want to sort them by the columns in B and D - there are headers. B is a date, D is a number - so both in ascending sort order.
I adjusted some code I found and came up with this:
The main issues I'm having is in defining the range as it adjusts with every sheet (in terms of rows) so I tried to set it as UsedRange, but I keep getting errors in the .SetRange line depending on how I phrase the line.
As above, it says "Method "Range" of object global failed - but I also get requires object errors when I change the way I'm trying to refer to AllData as a range.
Do I really need to go in and define Last Row and Last Column Variables and define my range that way, using .Cells in the .SetRange line?
I want to sort them by the columns in B and D - there are headers. B is a date, D is a number - so both in ascending sort order.
I adjusted some code I found and came up with this:
VBA Code:
With WS2
Set AllData = .UsedRange
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B1"), SortOn:=xlSortValues, Order:=xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("D1"), SortOn:=xlSortValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange (Range(AllData))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
The main issues I'm having is in defining the range as it adjusts with every sheet (in terms of rows) so I tried to set it as UsedRange, but I keep getting errors in the .SetRange line depending on how I phrase the line.
As above, it says "Method "Range" of object global failed - but I also get requires object errors when I change the way I'm trying to refer to AllData as a range.
Do I really need to go in and define Last Row and Last Column Variables and define my range that way, using .Cells in the .SetRange line?