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.