The Animal
Active Member
- Joined
- May 26, 2011
- Messages
- 449
Hi.
I have recorded a sort for WE 22-01-16 worksheet and assigned to a button as below.
I have added a button to a number of additional worksheets on the same workbook that have a different names and have created buttons and assigned that macro to those buttons.
When I run the macro on those other worksheets the actual sort runs on the WE 22-01-16 worksheet rather than the actual one I have selected. I understand why its happening just don't know how to fix it.
How do I change the sort macro so it applies to only the new worksheet that I have copied it to rather than the one where I created it?
Any help would be great.
Sub SortWithBlanks()
' SortWithBlanks Macro
Range("A5:CH22").Select
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Add Key:=Range( _
"B5:B22"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"A-Z,Blanks", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WE 22-01-16").Sort
.SetRange Range("A5:CH22")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 1
Range("A30:CH35").Select
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Add Key:=Range( _
"B30:B35"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"A-Z,Blanks", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WE 22-01-16").Sort
.SetRange Range("A30:CH35")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
I have recorded a sort for WE 22-01-16 worksheet and assigned to a button as below.
I have added a button to a number of additional worksheets on the same workbook that have a different names and have created buttons and assigned that macro to those buttons.
When I run the macro on those other worksheets the actual sort runs on the WE 22-01-16 worksheet rather than the actual one I have selected. I understand why its happening just don't know how to fix it.
How do I change the sort macro so it applies to only the new worksheet that I have copied it to rather than the one where I created it?
Any help would be great.
Sub SortWithBlanks()
' SortWithBlanks Macro
Range("A5:CH22").Select
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Add Key:=Range( _
"B5:B22"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"A-Z,Blanks", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WE 22-01-16").Sort
.SetRange Range("A5:CH22")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.ScrollColumn = 70
ActiveWindow.ScrollColumn = 69
ActiveWindow.ScrollColumn = 68
ActiveWindow.ScrollColumn = 67
ActiveWindow.ScrollColumn = 65
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 1
Range("A30:CH35").Select
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("WE 22-01-16").Sort.SortFields.Add Key:=Range( _
"B30:B35"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"A-Z,Blanks", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("WE 22-01-16").Sort
.SetRange Range("A30:CH35")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub