deselecting cells in vba autofilter

[B-Man]

Sub MagicNumber()



        Dim wsSource    As Worksheet
        Dim wsDest      As Worksheet
   
        Set wsSource = ThisWorkbook.Worksheets("Form")
        Set wsDest = ThisWorkbook.Worksheets("Number")
       

   
Application.ScreenUpdating = False

       On Error Resume Next

            With wsDest.Range("N" & wsDest.Rows.Count).End(xlUp).Offset(1, 0)
                .Value = wsSource.Range("C3").Value           
                .Offset(0, 1).Value = wsSource.Range("C7").Value
                .Offset(0, 2).Value = wsSource.Range("C5").Value
                .Offset(0, 3).Value = wsSource.Range("C6").Value
                .Offset(0, 4).Value = wsSource.Range("C17").Value
                .Offset(0, 5).Value = wsSource.Range("C18").Value
                .Offset(0, 6).Value = Round(wsSource.Range("F22").Value * 1440, 0)
                .Offset(0, 7).Value = wsSource.Range("C14").Value
            End With
           
    ActiveWorkbook.Worksheets("Number").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Number").AutoFilter.Sort.SortFields.Add2 Key _
        :=Range("AC1:AC2"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
       
        With ActiveWorkbook.Worksheets("Number").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With



Application.ScreenUpdating = True

End Sub

I have this code to add some data to my list that I then sort using filter
For some reason after the macro is run cells N3:AC81 are selected yet data only goes to row 53..

I don't want anything selected.
it wasn't selected before the macro runs.

How do I get it to not select the cells? im guessing the auto filter does it?
also
Is there a better way to sort?
is there a better way to copy the data across?

 

[rollis13]

The macro you are showing has no reason to select cells N3:AC81. This means that your issue is to be searched elsewere. You probably have an event macro Worksheet_Change in Number sheet's module that gets triggered.
Did you Debug the macro ?

 

[B-Man]

no worksheet macros,
no other macros run.

yes debugged and it happens when I run the filter (as soon as this passes ".Apply" IF im not on that sheet.
If im on that sheet when it runs it doesn't select anything. but im never on that sheet when the macro runs.

        With ActiveWorkbook.Worksheets("Number").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

 

[rollis13]

Sorry, can't replicate your issues probably because I have no idea of the layout of your sheet's data.
Anyway, at the moment I don't like this On Error Resume Next so please temporally disable it and see if the macro interrupts with error, it shouldn't if the code is correct.

 

[Alex Blakenburg]

See if this helps at all.
Key change:
• Turned off On Error Resume Next
• Handled error if Autofilter not turned on
• Changed sort Key2 from AC1:AC2 to just AC1. You either need to give the full range ie 1st row to last row, or just give it 1 cell so that it uses the current reqion to work out the row range.
• Also change sort Key2 to explicitly include the Sheet name and not just put Range()

Sub MagicNumber()

     Dim wsSource    As Worksheet
     Dim wsDest      As Worksheet

     Set wsSource = ThisWorkbook.Worksheets("Form")
     Set wsDest = ThisWorkbook.Worksheets("Number")
 
     Application.ScreenUpdating = False

    'On Error Resume Next

     With wsDest.Range("N" & wsDest.Rows.Count).End(xlUp).Offset(1, 0)
         .Value = wsSource.Range("C3").Value
         .Offset(0, 1).Value = wsSource.Range("C7").Value
         .Offset(0, 2).Value = wsSource.Range("C5").Value
         .Offset(0, 3).Value = wsSource.Range("C6").Value
         .Offset(0, 4).Value = wsSource.Range("C17").Value
         .Offset(0, 5).Value = wsSource.Range("C18").Value
         .Offset(0, 6).Value = Round(wsSource.Range("F22").Value * 1440, 0)
         .Offset(0, 7).Value = wsSource.Range("C14").Value
     End With
      
     With wsDest
         If .AutoFilterMode = True Then
             .AutoFilter.Sort.SortFields.Clear
         Else
             .Range("AC1").AutoFilter
         End If
         .AutoFilter.Sort.SortFields.Add2 Key _
             :=.Range("AC1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
             DataOption:=xlSortNormal
           
         With .AutoFilter.Sort
             .Header = xlYes
             .MatchCase = False
             .Orientation = xlTopToBottom
             .SortMethod = xlPinYin
             .Apply
         End With
       
     End With
 
 Application.ScreenUpdating = True

End Sub

 

[B-Man]

nope still selects the same range.

I figured it was the range area that is being filtered so I unselected the filter, autofilled my formulas down to row 600 and selected the filter then rand the macro again and if im on that sheet it doesnt select it but if im not on that sheet it selects all the way down to row 600 now.

so its definitely selecting the cells in the filtered range.

since if the sheet is active its not selecting the cells I added this into the code after the filter and it has created a work around for the issue
its not a very large macro so I cant see it creating any issues. I could probably have activated the sheet ran the filter and changed back but this works...

wsDest.Activate
wsDest.Range("A1").Select
wsSource.Activate

 

[Alex Blakenburg]

As long as you have something that works for you. Appreciate the feedback.