Automatically Sort Tables with Headers (VBA)

dmacch24

New Member
Joined
Apr 10, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hi


I'm trying to automatically sort the 2 tables on a spreadsheet using the macro below, however when the macro runs, it sorts the headers to the bottom even though I wrote xlYes for header. I think this may have something to do with the fact that there is data above the headers because it works when there is nothing on top of the headers.

The tables are located from C2:D5 and C12:D15 with numbers in cells C1,D1,C11, and D11 above the headers counting the number of cells.

Any suggestions on how to fix? Thanks! I've attached a screenshot of the worksheet as well.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A1:D5")) Is Nothing Then
Range("c2").Sort Key1:=Range("c3"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
If Not Intersect(Target, Range("A1:D14")) Is Nothing Then
Range("c12").Sort Key1:=Range("c13"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,034
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,119,061
Messages
5,575,872
Members
412,689
Latest member
nhsmedic
Top