'How to alphabetically sort in a generic table with a VBA Macro in excel
I have the following code
Sub Package_Update()
Dim sheets_count As Integer
Dim sheet_number As String
Dim j As Integer
Dim i As Integer
Dim xTable As ListObject
Dim WoSh As Worksheet
'Cancella tutti i colli già presenti
For Each WoSh In ThisWorkbook.Worksheets
If WoSh.Name <> "Traduzioni" And WoSh.Name <> "LISTA MATERIALE" And WoSh.Name <> "Collo" Then
Application.DisplayAlerts = False
WoSh.Delete
Application.DisplayAlerts = True
End If
Next WoSh
'Crea un nuovo foglio di nome "Collo"+valore (se non esiste già) per ogni valore trovato nella colonna i a partire dalla riga 18
sheet_count = Range("I1:I3000").Rows.Count
For j = 1 To sheet_count
sheet_number = Sheets("LISTA MATERIALE").Range("I18:I3000").Cells(j, 1).Value
'Controlla se il nome del foglio da creare esiste già e se la cella è vuota
If SheetCheck(sheet_number) = False And sheet_number <> "" Then
'Se il foglio non esiste già e la cella non è vuota copia il foglio collo e lo rinomina correttamente
Sheets("Collo").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Collo" & sheet_number
'Successivamente copia i materiali appartenenti ad uno specifico collo nel foglio del proprio collo
For Each xTable In Sheets("Collo" & sheet_number).ListObjects
Sheets("LISTA MATERIALE").ListObjects("Tabella10").Range.Copy _
Destination:=Sheets("Collo" & sheet_number).Range("A17")
Sheets("Collo" & sheet_number).ListObjects(xTable.Name).Range.AutoFilter Field:=9, Criteria1:= _
sheet_number
Next
'Se il foglio esiste già e la cella non era vuota la macro va avanti senza fare nulla
'(non dovrebbe mai entrare in questa parte del ciclo, il codice viene lasciato solo ai fini di testing futuro)
ElseIf SheetCheck(sheet_number) = True And sheet_number <> "" Then 'Do Nothing
'In qualsiasi altro caso la macro va avanti senza fare nulla, anche in questa parte del ciclo la macro non
'dovrebbe entrare mai, viene lasciato il codice solo per fini di testing futuro
Else
End If
Next j
'Torna alla lista materiale
Worksheets("LISTA MATERIALE").Activate
End Sub
The important things to know about this code is that it creates some news sheets in the excel file named as I requested and put into them the table I want copying it from an existing sheet.
Now here's my problem: In the code I posted this part
For Each xTable In Sheets("Collo" & sheet_number).ListObjects
Sheets("LISTA MATERIALE").ListObjects("Tabella10").Range.Copy _
Destination:=Sheets("Collo" & sheet_number).Range("A17")
Sheets("Collo" & sheet_number).ListObjects(xTable.Name).Range.AutoFilter Field:=9, Criteria1:= _
sheet_number
set the filter for the 9th column of the table in order to show what I need, now in column 3 of the table instead I have a description, for every row there is a description, like for example "CURVA SGHEMBA SALITA DX 300X75" I want to alphabetically sort this descriptions on the column 3, right after the new sheets are created. I have done it with the macro recoder and I obtained this code that is referred to the specific tablet in the specific sheet where I clicked
ActiveWorkbook.Worksheets("Collo1").ListObjects("Tabella177").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Collo1").ListObjects("Tabella177").Sort.SortFields. _
Add2 Key:=Range("Tabella177[[#All],[Descrizione articolo]]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Collo1").ListObjects("Tabella177").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
the problem is that this code is referring to a specific table (tabella177) and a specific sheet (Collo1), but I want the macro to do it in every table in every sheet created so I need to make it general, in particular I don't know how to do in this point
Add2 Key:=Range("Tabella177[[#All],[Descrizione articolo]]"), SortOn:= _
In order to make you understand I post also a screenshot, essentially I need the macro to do the action on the image on her own that is alphabetically sorting the descriptions in the column 3
I thought about something like this but doesn't work :'(
ActiveWorkbook.Worksheets("Collo" & sheet_number).Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Collo" & sheet_number).ListObjects(xTable.Name).Sort.SortFields. _
Add2 Key:=Range(xTable.Name, "[Descrizione articolo]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Collo" & sheet_number).ListObjects(xTable.Name).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
UPDATE: I did it, in the end the code working was this one
Sheets("Collo" & sheet_number).ListObjects(xTable.Name).Sort.SortFields.Clear
Sheets("Collo" & sheet_number).ListObjects(xTable.Name).Sort.SortFields.Add2 Key:=Range("C18:C2999"), Order:=xlAscending
With Sheets("Collo" & sheet_number).ListObjects(xTable.Name).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

