VBA: How To Sort Rows with formulas?

[Peter h]

I've got a pretty simple table of data, that I'm trying to sort, and am having trouble figuring out how to do it. In column A, I've got unique values from a full table of data on another sheet, Columns B, C, and D all have formulas that are generating values based on what's in column A of that row. I'm trying to write a macro that when you select the header of a column, it will sort the 4 columns in ascending order based on the selected column. My problem is that it's doing that in a way that it is changing the order of the formulas, rather than just sorting column A, and leaving the formulas alone. Can anyone explain to me how I can sort Column A based on the values in the other columns, without sorting my formulas? Here's my code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim wks                 As Worksheet
    Dim tar_RNG             As Range
    Dim d_RNG               As Range
    
    Application.ScreenUpdating = False
    
    Set wks = ThisWorkbook.Sheets(Target.Parent.Name)
    Set tar_RNG = wks.Range("A1:D1")
    Set d_RNG = wks.Range(wks.Cells(1, 1), wks.Cells(wks.Cells(Rows.Count, 1).End(xlUp).Row, 4))
    
    If Not Application.Intersect(Target, tar_RNG) Is Nothing Then
        d_RNG.Sort Key1:=Target, Order1:=xlAscending, Header:=xlYes
    End If
    
    Set d_RNG = Nothing
    Set tar_RNG = Nothing
    Set wks = Nothing
    Application.ScreenUpdating = True
End Sub

Sheet:

Header: Location	Header: # of Procedures	Header: AVG Days to Heal	Header: # of Cases w/ Complications
Left: Arch	=countif('Tenex DFU'!$f:$:4,Analytics!A2	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A2,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A2,'Tenex DFU'!O:O,"Yes")
Left: Cuneiform	=countif('Tenex DFU'!$f:$:4,Analytics!A3	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A3,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A3,'Tenex DFU'!O:O,"Yes")
Left: Distal Phalanx Great Toe	=countif('Tenex DFU'!$f:$:4,Analytics!A4	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A4,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A4,'Tenex DFU'!O:O,"Yes")
Left: Fifth IPJ	=countif('Tenex DFU'!$f:$:4,Analytics!A5	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A5,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A5,'Tenex DFU'!O:O,"Yes")

Output:

Header: Location	Header: # of Procedures	Header: AVG Days to Heal	Header: # of Cases w/ Complications
Left: Arch	1	7.0	0
Left: Cuneiform	1	18.0	0
Left: Distal Phalanx Great Toe	2	34.5	1
Left: Fifth IPJ	1	0.0	0

After Sorting using Col C (as an example):

Header: Location	Header: # of Procedures	Header: AVG Days to Heal	Header: # of Cases w/ Complications
Left: Fifth IPJ	=countif('Tenex DFU'!$f:$:4,Analytics!A5	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A5,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A5,'Tenex DFU'!O:O,"Yes")
Left: Arch	=countif('Tenex DFU'!$f:$:4,Analytics!A2	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A2,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A2,'Tenex DFU'!O:O,"Yes")
Left: Cuneiform	=countif('Tenex DFU'!$f:$:4,Analytics!A3	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A3,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A3,'Tenex DFU'!O:O,"Yes")
Left: Distal Phalanx Great Toe	=countif('Tenex DFU'!$f:$:4,Analytics!A4	=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A4,'Tenex DFU'!$J:$J,"<>NA"),"NA")	=COUNTIFS('Tenex DFU'!F:F,Analytics!A4,'Tenex DFU'!O:O,"Yes")

My goal is to just sort column A based on the values in the selected column, but leave the formulas alone in the other 3 columns. Any suggestions or ideas?

Thanks so much.

 

[Fluff]

Try changing the formulae so that they do not use the active sheets name, like
=COUNTIFS('Tenex DFU'!F:F,A2,'Tenex DFU'!O:O,"Yes")

 

[Peter h]

Try changing the formulae so that they do not use the active sheets name, like
=COUNTIFS('Tenex DFU'!F:F,A2,'Tenex DFU'!O:O,"Yes")

I'll give that a try when I get a chance.

 

[Fluff]

 

[Peter h]

IT WORKED! Thank you soo much. Why is it that referencing the sheet name causes it to act differently?

 

[Fluff]

Why is it that referencing the sheet name causes it to act differently?

Absolutely no idea, I just learnt that it's better not to refer to the sheet that contains the formula.

 

[Peter h]

Absolutely no idea, I just learnt that it's better not to refer to the sheet that contains the formula.

Well, thanks for the heads up. I'll keep that in mind in the future.

 

[Fluff]

You're welcome & thanks for the feedback.