Macro only works on initial worksheet

[AvoidingVBA]

I'm working on a template that allows me to repeatedly copy the initial worksheet (e.g. sheet 1) and have multiple worksheets in a single file. I do repetitive data entry/manipulation and a system like this is key. I have a variety of macros I use for this workbook, some assigned to buttons and others live in the ribbon. All of these are recorded since I'm unfamiliar with VBA.

I have a macro that creates a custom sort list for my data, since copying a worksheet doesn't also copy custom sort/filter settings. The problem is when I run the macro on any worksheet except the initial one (e.g. sheet 1(1)), it still sorts on the first worksheet, not the current worksheet. Does anyone know what causes this? Is there a step I can take in recording to prevent this?

Thanks

 

[Candyman8019]

It would help if you could share your macro code. It is likely a simple thing like using 'activesheet' vs a specific sheet.

 

[AvoidingVBA]

It would help if you could share your macro code. It is likely a simple thing like using 'activesheet' vs a specific sheet.

Yes, I'm unsure how to change it from activesheet when recording. Here's the code:

Sub Live_Sort()
'
' Live_Sort Macro
'

'
    ActiveWorkbook.Worksheets("Copy Sheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Copy Sheet").Sort.SortFields.Add2 Key:=Range( _
        "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Copy Sheet").Sort.SortFields.Add(Range("B11:B54"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
        , 240)
    ActiveWorkbook.Worksheets("Copy Sheet").Sort.SortFields.Add(Range("B11:B54"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
        230, 230)
    ActiveWorkbook.Worksheets("Copy Sheet").Sort.SortFields.Add2 Key:=Range( _
        "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Copy Sheet").Sort
        .SetRange Range("A10:J54")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Candyman8019]

Give this a try:
change instances of:
ActiveWorkbook.Worksheets("Copy Sheet").
to:
ActiveWorkbook.ActiveSheet.

 

[Fluff]

How about

Sub Live_Sort()
'
' Live_Sort Macro
'

'
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
         "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
         "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
         , 240)
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
         230, 230)
      .SortFields.Add2 Key:=Range( _
         "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortTextAsNumbers
    
       .SetRange Range("A10:J54")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub

 

[Candyman8019]

How about

Sub Live_Sort()
'
' Live_Sort Macro
'

'
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
         "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
         "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
         , 240)
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
         230, 230)
      .SortFields.Add2 Key:=Range( _
         "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortTextAsNumbers
   
       .SetRange Range("A10:J54")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub

Nice and clean

 

[AvoidingVBA]

Give this a try:
change instances of:
ActiveWorkbook.Worksheets("Copy Sheet").
to:
ActiveWorkbook.ActiveSheet.

This works great (and helps me fix other macros I was having a similar issue with). Thanks!

 

[AvoidingVBA]

How about

Sub Live_Sort()
'
' Live_Sort Macro
'

'
   With ActiveSheet.Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range( _
         "A11:A54"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
         "Equity,Allocation,Unknown,BDC,Fixed,Cash/Equiv", DataOption:=xlSortNormal
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 176 _
         , 240)
      .SortFields.Add(Range("B11:B54"), _
         xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(231, _
         230, 230)
      .SortFields.Add2 Key:=Range( _
         "B11:B54"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
         xlSortTextAsNumbers
   
       .SetRange Range("A10:J54")
       .Header = xlYes
       .MatchCase = False
       .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
       .Apply
   End With
End Sub

This worked perfectly. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.