excel learner
asked on
Sorting the sheets in an excel file
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
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
Hi,
please use the code attached below. or you can check it in the attached workbook.
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
SORT-SHEETS-by-VBA.xlsm
Here it is this gives you the choice to sort ascending or descending !!!
gowflow
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
gowflow
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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"
Right-click on the arrow keys on the extreme left of the sheet tabs and select "More sheets"
@Excellearner
did you try using the solution, i have provided you comment ID ID: 40559610 ?
did you try using the solution, i have provided you comment ID ID: 40559610 ?
ASKER
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
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
Hi,
in my code
#a40559616
You can replace and add the sheet names that you want to be together at line 4
Regards
in my code
#a40559616
You can replace and add the sheet names that you want to be together at line 4
Regards
Are you looking for something like this?
Sub collectsheets()
For Each shn In Split("Sheet1,Sheet3,Sheet 4,Sheet6,S heet8,Shee t9,Sheet10 ", ",")
Worksheets(shn).Move Worksheets(1)
Next shn
End Sub
Sub collectsheets()
For Each shn In Split("Sheet1,Sheet3,Sheet
Worksheets(shn).Move Worksheets(1)
Next shn
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
It worked, thank you
Allow me to try it in the live files and will comeback with observations.
thank you fo ryour prompt response.
ASKER
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
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
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
gowflow
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Good piece of edit gowflow :-)
Any chance to hv made up your mind on the suitable solution ?
gowflow
gowflow
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.
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.
gowflow