Sort data in one sheet when sorting ranges in other sheet

Viewed 18

In Excel, I have Sheet 1 "LIST". This sheet has a list of variable amount people names in A1 to any row, currently 10 people (dynamic range). I have a macro to sort them alphabetically:

Private Sub CommandButton2_Click()
    Dim altura As String, rango As Variant
    altura = Range("A1:A10").Cells.SpecialCells(xlCellTypeConstants).Count
    rango = Range("A1:K" & altura).Address
    ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Hoja1").Sort
        .SetRange Range(rango)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

Those names have each one a dedicated row in Sheet 2 "DATA", where I enter data in 4 extra contiguous columns (B, C, D, E), being A column populated with the formula =LIST!A__ to keep track of the names from "LIST" (different sheet but the same range). The 4 columns data in "DATA" are manually entered and not formula-dependent on A column.

I would like to get to add a new name in empty LIST!A11 cell, then sort alphabetically this whole name list (this is now achieved through the macro above), and then sort/move every user data in "DATA" accordingly, like this:

"LIST" sheet: I have names and add a new one in the first free cell in A. ("CName")

Name Sht1
AName
BName
DName
EName
(etc)
CName

Then I sort alphabetically.

"DATA" sheet: I have data for each name. I want to have it like this after sorting "LIST" names list, and for any number of list entries (rows with data):

Name Sht2 Data1 Data2
AName Adata1 Adata2
BName Bdata1 Bdata2
CName (empty) (empty)
DName Ddata1 Ddata2
EName Edata1 Edata2
#name #data1 #data2

Thank you. If clarification needed, please ask.

0 Answers
Related