Sorting Multiple Sheets Simultaneously!

[MathMakesMeMad]

Hello Crew!
I'm really new on this forum and I'm getting used with VBA for excel. So I'm not an expert yet. Please forgive me!
Basically, I have 7 sheets, each one based on different issues. Below I'll write an example, just to have a simple idea. (Columns A to F)

Sheet 1

CODE COUNTRY POPULATION NAME LANGUAGE AVERAGE

1	ITALY	1,000,000	italian	italian	27
2	SPAIN	2,500,000	spanish	spanish	25
3	BRAZIL	30,000,000	brazilian	portuguese	23
4	CANADA	17,000,000	canadian	english	24
5	FRANCE	1,800,000	french	french	26

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2 (=Sheet1!A2) (=Sheet1!B2) (=Sheet1!C2)
CODE COUNTRY POPULATION MOUNTAIN LAKE EXTENSION

1	ITALY	1,000,000	Bianco	Garda	1500
2	SPAIN	2,500,000	Teide	Zzz	1800
3	BRAZIL	30,000,000	Yyyy	That	2030
4	CANADA	17,000,000	Xxx	Dontknow	1900
5	FRANCE	1,800,000	Blanche	Xyz	2700

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 3
CODE COUNTRY POPULATION RELIGION MEMBERS LABEL

1	ITALY	1,000,000	Catholic	1,200,000	C
2	SPAIN	2,500,000	Blablabla	2,000,000	E
3	BRAZIL	30,000,000	Catholic	1,000,000	A
4	CANADA	17,000,000	British	6,000,000	G
5	FRANCE	1,800,000	Protestant	3,000,000	F

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

sheet 4/sheet 5...
and so on.
Always with the first 3 columns A to C repeated and mirrored.
I'm struggling to create a code which can sort all the sheets simultaneously, when I sort by Code, or by country, or by population in the first sheet, given that all those 3 columns are the same in the 7 sheets.
Obviously, If I sort only the first sheet, then it reflects into A-B-C columns of the others sheets, but all related data in columns D to F are messed up!
I guess it's quite simple code, but I don't know how to sort this out!!
Please help!
Any suggestion?
Thanks
Vito

 

[mumps]

Try:

Sub SortSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim response As String
    Dim ws As Worksheet
    response = InputBox("Please enter the column letter of the column you wish to sort.")
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Sort.SortFields.Clear
        ws.Sort.SortFields.Add Key:=Range(response & "2:" & response & LastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ws.Sort
            .SetRange Range("A1:F" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[MathMakesMeMad]

Try:

Sub SortSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim response As String
    Dim ws As Worksheet
    response = InputBox("Please enter the column letter of the column you wish to sort.")
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Sort.SortFields.Clear
        ws.Sort.SortFields.Add Key:=Range(response & "2:" & response & LastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ws.Sort
            .SetRange Range("A1:F" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

Thanks for your quick response, your great! seriously, it works!
However, testing the macro I found out other two issues I would like to solve,
1-The sheets 2-3-4-5...and following, can't have instanced values on columns A-B-C, example: cell Sheet 2/A2: =Sheet1!A2; cell Sheet 2/B2: =Sheet1!B2 and so on.
2-I would like to have more rows of headers, that means, from Row 1 to 5 and starting sorting from row 6, as I need to put more information for the headers, do you think it would be possible?

Thank you so much in advance!
Meanwhile, I keep testing!
Vito

 

[MathMakesMeMad]

YEEEES
For the point number 2 it is possible, I just changed .SetRange Range("A1:F" & LastRow) to .SetRange Range("A6:F" & LastRow)
and it works perfectly!!!

GREEAT!
So now I need just to sort the first point out!
I keep testing.
vito

 

[mumps]

I don't quite follow what you mean by:

1-The sheets 2-3-4-5...and following, can't have instanced values on columns A-B-C, example: cell Sheet 2/A2: =Sheet1!A2; cell Sheet 2/B2: =Sheet1!B2 and so on.

Could you please clarify with a more detailed description of what you want to do.

 

[MathMakesMeMad]

Your macro works well only if all the cells in the tables are unique.
If I run the macro for column B in sheet1 it sorts exactly all the rows in all the sheets.
That's great.
However, as you can see in my example, sheet2 and 3 have the first 3 columns instanced. That means, B2 in sheet1 is italy, and B2 in sheet2 is: =sheet1!b2.
Testing your macro, the columns D-E-F don't follow the ordering of the rows, but the cells go wrong.
Probably i did something wrong, but that's what happens with this table.
Your macro it's ok and I could already use it, but in this way, I can't make any link among the sheets.

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[MathMakesMeMad]

Thanks!
You are absolutely right.
Anyway, the excel is still under construction, even because of this issue we are discussing for.
Here is the link of the excel from where I conceptually started building it.
https://app.box.com/s/rbysojeyzt8wb2s1l9lydq1oio3wjuud
As you can see columns A-B-C contain always the same data (instanced). By contrast, if we run that macro, the data in column D-E-F result messed up!
I need to keep the data in a separate sheet, as every sheet match a different process. However, I would like to keep links among the sheets.
It's like having just one unique sheet, but split in different sheets divided for process.
Together with some relevant information of the rows of sheet1 contained in columns A-B-C.
Hope this can help!
Thank you so much!
V

 

[mumps]

The formulas that you were using to reference the first sheet were causing the problem. The macro below eliminates the need for the formulas because it simply copies the first 3 columns from the "Order Details" sheet to all the other sheets and then sorts them. Hopefully, this works out for you (speriamo!!!).

Sub Ordinare()
'
' Numero_Ordine Macro
'
' Keyboard Shortcut: Ctrl+o
'
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim response As String
    Dim ws As Worksheet
    Dim bottomC As Long
    bottomC = Sheets("Order Details").Range("C" & Rows.Count).End(xlUp).Row
    response = InputBox("Inserisci la lettera della colonna da ordinare!")
    For Each ws In Sheets
        If ws.Name <> "Order Details" Then
            Sheets("Order Details").Range("A3:C" & bottomC).Copy ws.Range("A3")
        End If
    Next ws
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Sort.SortFields.Clear
        ws.Sort.SortFields.Add Key:=Range(response & "2:" & response & LastRow), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ws.Sort
            .SetRange Range("A2:H" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[MathMakesMeMad]

Amazing!
You must be a genius!
It works perfectly and it's exactly what I was looking for.
Thanks and I hope this can support some other user on the web!!
Thanks again
V