Auto Sort Column Descending - Data In Column Is Taken From Another Sheet Using =IF Function

[98aallen]

Hi,

Wonder if anyone can help, im trying to do this in VBA but im very new to it and I cant get it to work, i think maybe im pasting the codes into the wrong sheets etc.

Basically i have a column (B) in sheet3 which has a list of addresses, the data in this column is taken from a column (A) in sheet3 which then takes its data from 2 columns (D) & (J) in sheet 1, sounds complicated but its the only way i knew how to do it.

So in a nut shell, the data in sheet3 contains data which is not manually entered directly in that cell.

is there a way to automatically sort this column (B) in sheet 3 when data in sheet1 is entered which then effects if an address appears in sheet 3 column (B) or not.

I hope someone is with me an can help.

Plus where to paste the code would be helpfull, in the a specific sheet etc?

 

[RickXL]

Hi,

I think this works:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Or Target.Column = 10 Then
        With ThisWorkbook.Worksheets("Sheet3")
            .UsedRange.Sort key1:=.Range("B1"), _
                order1:=xlDescending, Header:=xlYes
        End With
    End If
End Sub

The code needs to be pasted in to the sheet module for Sheet1.

When anything on SHeet1 changes that macro will run.
If the column that changed was either 4 (D) or 10 (J) then the Sort statement will run.
The sort statement is pointed to Sheet3 in the current Workbook.


Regards,