SolvedPrivate

Sorting the sheets in an excel file

Posted on 2015-01-20
21
19 Views
Last Modified: 2016-02-10
Dear experts,

I have an active file which has 55 sheets, but these are not in the order I wish to have to make it easy for me to navigate to the sheets.

Now, can I request a vba query which can gather together the sheet and place these to the left (or the beginning, like sheet 1).
The vba will have the capability to include the list of 15 sheet names, like for instance
Sheet1
Sheet 2
Sheet 3…
Sheet 15


Thank you
0
Comment
Question by:Excellearner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +3
21 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 40559604
yes sure you want it in which order ascendant like A-Z or Z-A ?
gowflow
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40559610
Hi,

please use the code attached below.   or you can check it in the attached workbook.

Sub Sort_Active_Book_Sheets()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For i = 1 To Sheets.Count
      For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
        ShNameWithoutNr = fStringWithoutEndingNumber(Sheets(j).Name)
        ShPlus1NameWithoutNr = fStringWithoutEndingNumber(Sheets(j + 1).Name)
        ShNameEndNr = fEndingNumber(Sheets(j).Name)
        ShPlus1NameEndNr = fEndingNumber(Sheets(j + 1).Name)
         
         If iAnswer = vbYes Then

            If ShNameWithoutNr > ShPlus1NameWithoutNr Or _
               ShNameWithoutNr = ShPlus1NameWithoutNr And ShNameEndNr * 1 > ShPlus1NameEndNr * 1 Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
'
' If the answer is No, then sort in descending order.
'
         ElseIf iAnswer = vbNo Then
            If ShNameWithoutNr < ShPlus1NameWithoutNr Or _
               ShNameWithoutNr = ShPlus1NameWithoutNr And ShNameEndNr * 1 < ShPlus1NameEndNr * 1 Then
               Sheets(j).Move After:=Sheets(j + 1)
            End If
         End If
      Next j
   Next i
End Sub

Function fStringWithoutEndingNumber(myStr As String) As String
For Idx = Len(myStr) To 1 Step -1
    If Mid(myStr, Idx, 1) Like "[0-9]" And FoundLetter <> True Then
        tmp = ""
    Else
        tmp = Mid(myStr, Idx, 1)
        FoundLetter = True
    End If
    Res = tmp & Res
Next
fStringWithoutEndingNumber = Res
End Function

Function fEndingNumber(myStr As String) As String
For Idx = Len(myStr) To 1 Step -1
    If Mid(myStr, Idx, 1) Like "[0-9]" And FoundLetter <> True Then
        tmp = Mid(myStr, Idx, 1)
    Else
        tmp = ""
        FoundLetter = True
    End If
    Res = tmp & Res
Next
fEndingNumber = Res
End Function

Open in new window

SORT-SHEETS-by-VBA.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40559612
Here it is this gives you the choice to sort ascending or descending !!!

Sub Sort_Active_Book()
Dim I As Integer
Dim J As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
   iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
     & "Clicking No will sort in Descending Order", _
     vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
   For I = 1 To Sheets.count
      For J = 1 To Sheets.count - 1
'
' If the answer is Yes, then sort in ascending order.
'
         If iAnswer = vbYes Then
            If UCase$(Sheets(J).Name) > UCase$(Sheets(J + 1).Name) Then
               Sheets(J).Move After:=Sheets(J + 1)
            End If
'
' If the answer is No, then sort in descending order.
'
         ElseIf iAnswer = vbNo Then
            If UCase$(Sheets(J).Name) < UCase$(Sheets(J + 1).Name) Then
               Sheets(J).Move After:=Sheets(J + 1)
            End If
         End If
      Next J
   Next I
End Sub

Open in new window


gowflow
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 52

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 50 total points
ID: 40559616
Hi,

for the first 15 you could use this

Sub macro1()
Dim SortOrder As Variant
Dim Idx As Long
SortOrder = Array("Sheet1", "Sheet2", "Sheet15")
For Idx = UBound(SortOrder) To LBound(SortOrder) Step -1
    Worksheets(SortOrder(Idx)).Move before:=Worksheets(1)
Next
End Sub

Open in new window

but what are we doing of the rest

Regards
0
 

Author Comment

by:Excellearner
ID: 40559627
All,

A zillion apologies

the sheet names will not be sheet1 nor do i need it a-z or z-a order

the sheet names will be random and i wish to place these adjacent to each other.

Thank you for your effort.

apologies once again.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40559630
As an additional tool for selection of sheets

Right-click on the arrow keys on the extreme left of the sheet tabs and select "More sheets"
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40559635
@Excellearner

did you try using the solution, i have provided you  comment ID ID: 40559610  ?
0
 

Author Comment

by:Excellearner
ID: 40559644
ProfessorJimJam

thank you for your comment

I have shuffled the sheet numbers and ran the macro. The macro has put the sheets in ascending order.

Did i miss anything.

thank you
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40559649
Hi,

in my code
#a40559616

You can replace and add the sheet names that you want to be together at line 4

Regards
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40559650
Are you looking for something like this?

Sub collectsheets()
For Each shn In Split("Sheet1,Sheet3,Sheet4,Sheet6,Sheet8,Sheet9,Sheet10", ",")
Worksheets(shn).Move Worksheets(1)
Next shn
End Sub
0
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 225 total points
ID: 40559664
Excellearner

ok, check the attached file.

you need to put the sheets name in column A and the way you want them to be ordered. then run the macro and the macro will sort based on the sort order in Column A. see attached.
SheetSort.xlsm
0
 

Author Comment

by:Excellearner
ID: 40559677
ProfessorJimJam,

It worked, thank you
Allow me to try it in the live files and will comeback with observations.

thank you fo ryour prompt response.
0
 

Author Comment

by:Excellearner
ID: 40559680
Experts,

Thank you for your comments.

I will soon be using this macro.

I will try each on fo the suggestions and comeback with my obsevations.

thank you
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40559738
When you say side by side do you mean in different windows ? or sorted ? if sorted at in a certain way you should have specified this originally in your question we cannot guess !!!

gowflow
0
 

Author Comment

by:Excellearner
ID: 40559744
Gowflow,

Side by side - adjacent sheets in the same file. In other words, the sheets I mention will be place at the beginnin in the order i have mentioned in the vba and any other sheet not listed for the vba will be placed (moved) after the list I have mentioned.

Sorry for my sh*t language.

thank you
0
 
LVL 31

Assisted Solution

by:gowflow
gowflow earned 225 total points
ID: 40559816
Here is a code that will assume that you have some sheets that you want to have in a certain order that you will define and the rest will go at the end or just after the last sheet that was specified.

The list is put in Sheet1 in Col A. It has also a check if the sheet is mistyped and does not exist. after running the focus will comeback to the sheet where you have the list.

Now this macro is not dependent on Sheet1 you can actually move it to your workbook and whatever sheet you decide to use and put in Col A the list will be the one used no need to change its name as long as you run the macro when you are in this sheet.

Here is the code and the rest is in the attached workbook.

Sub SpecificSort()
On Error GoTo ErrHandler

Dim WS As Worksheet
Dim rng As Range
Dim sNames As String
Dim cCell As Range

Set rng = ActiveSheet.Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))

'---> Set them in the desired order
For Each cCell In rng.Cells
    Sheets(cCell.Value).Move after:=Sheets(Sheets.Count)
    If sNames <> "" Then sNames = sNames & ";"
    sNames = sNames & cCell.Value
Next

'---> Put the rest at the end
For Each WS In ActiveWorkbook.Worksheets
    If InStr(1, sNames, WS.Name) = 0 Then
        WS.Move after:=Sheets(Sheets.Count)
    End If
Next WS

'---> Activate orignal sheet
rng.Parent.Activate

Exit Sub

ErrHandler:
If Err = 9 Then
    MsgBox "Sheet " & cCell.Value & " does not exist !"
    Resume Next
End If
End Sub

Open in new window



gowflow
Specific-Sort.xlsm
0
 

Author Comment

by:Excellearner
ID: 40560084
Gowflow,

Perfecto, as usual.

I will try it on all my files and close the question.

I will comeback with my responses to the rest of the suggested solutions.

thank you
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40560098
Good piece of edit gowflow :-)
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40571074
Any chance to hv made up your mind on the suitable solution ?
gowflow
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 40626907
I've requested that this question be closed as follows:

Accepted answer: 500 points for gowflow's comment #a40559816

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question