HELP pls..urgent

Bilaal xaka

New Member
Joined
Mar 7, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Hello everyone,
I need your help pls.
I have a workbook where there are 5 sheets, each sheet with 3tables, and every sheet has the same table size and column with same header at the exact cell number, but different data within each table.(pls find attached)
Is it possible than I sort all the 3 tables of all the 5 sheets (15 tables) in ascending order in cells C1, B16& I16 at one go?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
Very urgent until you forget the attachment? ;)

I don't think it is possible to sort 3 table at the same time, but why it can't be done sequentially? Probably not much of time difference.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
Even using VBA also the process is sorting one table at a time no matter what they say. It is just that it runs automatically and you need to run once. If interested, then here's the link that shows how

 

Bilaal xaka

New Member
Joined
Mar 7, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Even using VBA also the process is sorting one table at a time no matter what they say. It is just that it runs automatically and you need to run once. If interested, then here's the link that shows how

Hey dear,
Thank you very much for this link.
Im weak in excel, could you please further help me with this.

Suppose I have a table (E8:H15) with the same table size in every sheet, E8:H8 being the header of the table. (obv data within the table in each sheet is different)
How can I apply the VBA code here? in sorting the table for all sheets in terms of column G (E9:H15)(excluding the header) in ascending order, while excluding zeros and blanks within the table.

Looking forward for your answer. Thanks
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Try his code. I temporarily add helper column I to convert any zero in column G to blank to make sorting ignore zero.

VBA Code:
Sub SortAllSheets()

Dim ws As Worksheet
Dim rngData As Range

Application.ScreenUpdating = False
For Each ws In Sheets
    ws.Range("I8") = "Sort"
    ws.Range("I9", "I15").Formula = "=IF(G9=0,"""",G9)"
    Set rngData = ws.Range("E8", "I15")
    rngData.Select
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("I9:I15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("E8:I15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("I8", "I15").Clear
    ws.Range("E8").Select
Next

End Sub
 

Bilaal xaka

New Member
Joined
Mar 7, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Try his code. I temporarily add helper column I to convert any zero in column G to blank to make sorting ignore zero.

VBA Code:
Sub SortAllSheets()

Dim ws As Worksheet
Dim rngData As Range

Application.ScreenUpdating = False
For Each ws In Sheets
    ws.Range("I8") = "Sort"
    ws.Range("I9", "I15").Formula = "=IF(G9=0,"""",G9)"
    Set rngData = ws.Range("E8", "I15")
    rngData.Select
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("I9:I15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("E8:I15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("I8", "I15").Clear
    ws.Range("E8").Select
Next

End Sub
Hey Zot,
Unfortunately its not working as it should. could you please further help.

Please find attached pictures.
Capture1 shows the error message when i run the VBA code
Capture2 is the result of the debug
Capture3 is sheet 1 after it has been sorted (rightly sorted)
Capture4 is sheet 2 after it has been sorted (wrongly sorted)
Capture5 is sheet 3 & sheet 4 after it has been sorted (wrongly sorted)(same results)

To conclude, only sheet 1 has been rightly sorted.
Please help
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    68.7 KB · Views: 7
  • Capture2.PNG
    Capture2.PNG
    65.7 KB · Views: 7
  • Capture3.PNG
    Capture3.PNG
    39.7 KB · Views: 7
  • Capture4.PNG
    Capture4.PNG
    40.1 KB · Views: 6
  • Capture5.PNG
    Capture5.PNG
    33.9 KB · Views: 6

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Sorry I did not test for subsequent sheet. My fault. The problem is unnecessary use of Select which will cause error unless the sheet is activated but was not during the loop. I have modified the code. This should work.

VBA Code:
Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
    ws.Range("I8") = "Sort"
    ws.Range("I9", "I15").Formula = "=IF(G9=0,"""",G9)"
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("I9:I15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("E8:I15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("I8", "I15").Clear
    Application.Goto ws.Range("A1"), True
Next

End Sub
 

Bilaal xaka

New Member
Joined
Mar 7, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
Sorry I did not test for subsequent sheet. My fault. The problem is unnecessary use of Select which will cause error unless the sheet is activated but was not during the loop. I have modified the code. This should work.

VBA Code:
Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
    ws.Range("I8") = "Sort"
    ws.Range("I9", "I15").Formula = "=IF(G9=0,"""",G9)"
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("I9:I15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("E8:I15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("I8", "I15").Clear
    Application.Goto ws.Range("A1"), True
Next

End Sub
Hey Zot.. the issue is that when I sort the table, the blanks and the zero rows are sorted on the top (as attached)
Any solution plsss
Thank you so much.
 

Attachments

  • Capture.PNG
    Capture.PNG
    41.8 KB · Views: 7

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,174
Office Version
  1. 2016
Platform
  1. Windows
That was because the code was sorting number. SO, space is sorted at the back. Now data is alphabet. Blanks go to the top. The easiest way is to replace the Blank with "ZZZ". This will be at the back unless you have name like with ZZZ as prefix like ZZA ;)

This should work

VBA Code:
Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
    ws.Range("I8") = "Sort"
    ws.Range("I9", "I15").Formula = "=IF(G9=0,""ZZZ"",G9)"
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("I9:I15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("E8:I15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("I8", "I15").Clear
    Application.Goto ws.Range("A1"), True
Next

End Sub
 

Bilaal xaka

New Member
Joined
Mar 7, 2021
Messages
10
Office Version
  1. 2013
Platform
  1. Windows
That was because the code was sorting number. SO, space is sorted at the back. Now data is alphabet. Blanks go to the top. The easiest way is to replace the Blank with "ZZZ". This will be at the back unless you have name like with ZZZ as prefix like ZZA ;)

This should work

VBA Code:
Sub SortAllSheets()

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In Sheets
    ws.Range("I8") = "Sort"
    ws.Range("I9", "I15").Formula = "=IF(G9=0,""ZZZ"",G9)"
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=Range("I9:I15"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ws.Sort
        .SetRange Range("E8:I15")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ws.Range("I8", "I15").Clear
    Application.Goto ws.Range("A1"), True
Next

End Sub
Hey Zot,
Thanks. you are the best.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top