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
50,928
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,118,861
Messages
5,574,705
Members
412,613
Latest member
EFRATA
Top