Sort ascending and/or descending based of headers

I want to sort headers ascending if they are sorted descending and descending if they are sorted ascending.

At the moment I have the following code:

Sub Sort1()
    Range("B10").CurrentRegion.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlYes
End Sub

How can I do this?

Thank you for your quick reply! Unfortunately the code doesn't allow me to click one time to sort ascending and then click one time again and sort descending.

Hi, thank you for your reply! You are correct, click the header one time and it sorts descending/ascending. Click again and it sortes vice versa!

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Static x As Long
   If Intersect(Target, Range("B10").CurrentRegion.Rows(1)) Is Nothing Then Exit Sub
   If x = 0 Then x = 1
   Range("B10").CurrentRegion.Sort Key1:=Target, order1:=x, Header:=xlYes
   x = IIf(x = 1, 2, 1)
   Cancel = True
End Sub

Thank you Fluff13, your code works flawlessly!

Would it be possible to have 3 instances of the code running? For 3 different tables I am filtering.

What are the ranges for each table?

Table1 has range B10:O300

Table2 has range B303:O593

Table3 has range B596:O886

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Static x As Long, y As Long, z As Long
   If Not Intersect(Target, Range("B10:O10")) Is Nothing Then
      If x = 0 Then x = 1
      Range("B10").CurrentRegion.Sort Key1:=Target, order1:=x, Header:=xlYes
      x = IIf(x = 1, 2, 1)
      Cancel = True
   ElseIf Not Intersect(Target, Range("B303:O303")) Is Nothing Then
      If y = 0 Then y = 1
      Range("B303").CurrentRegion.Sort Key1:=Target, order1:=y, Header:=xlYes
      y = IIf(y = 1, 2, 1)
      Cancel = True
   ElseIf Not Intersect(Target, Range("B596:O596")) Is Nothing Then
      If z = 0 Then z = 1
      Range("B596").CurrentRegion.Sort Key1:=Target, order1:=z, Header:=xlYes
      z = IIf(z = 1, 2, 1)
      Cancel = True
   End If
End Sub

Flawless! Thank you!

After I filter the 3 tables, ActiveSheet.ShowAllData only resets one table. Perhaps you know how to fix it?

Glad to help & thanks for the feedback

You'll need to loop through the tables, if you have problems with that, then you'll need to start a new thread.