• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rearrange Sheets back and forth

nagovind

nagovind

Member
Dear all,

Kindly advise the solution for the case

We have sheets in a worksheet as below

AP1sheet1
sheet2
sheet3
AP2sheet4
sheet5
sheet6
sheet7
AP3sheet8
sheet9
sheet10
AP3sheet11

[So this AP1 or AP2 or AP3 is 3 standard prefix is constant and repeated as many times as in many sheets]

Need a code to rearrange in one click as
sheet2
sheet3
sheet5
sheet6
sheet7
sheet9
sheet10
AP1sheet1
AP2sheet4
AP3sheet8
AP3sheet11

Then back to original after running a code
same as original

AP1sheet1
sheet2
sheet3
AP2sheet4
sheet5
sheet6
sheet7
AP3sheet8
sheet9
sheet10
AP3sheet11

Kindly advise

Thanks and Regards
Govind
 
Hi !

Rearrange is easy, at beginner level, as you can try
just activating Macro recorder & operating manually …

But please advise what is your logic, your algorithm,
to go « back to original » ?
​
 
Dear Marc,

Thank you for your reply

Logic is as below

Normal sheet names without having prefix AP has certain format

Sheet names begins with AP and followed by any sheet name has certain format content

So there are three prefix sheet name has AP1 AP2 and AP2
Required Algorithm shall be
Select all sheets
Check each sheet names
If any of the sheet names starts with AP2 or AP2 or AP2 move to end
Before moving to end it should know the previous sheet sequence to arrange it back to original pattern
Once after the sheet names starting with AP2 or AP2 or AP3 is moved to end

Another code will run to rearrange the sheets to previous location

Kindly do the needful

Regards
Govind
 
As I yet wrote, first rearrange is easy, at beginner level, so you can try …

What I asked is for :
nagovind said:
Another code will run to rearrange the sheets to previous location
Click to expand...
What is your logic, how to « rearrange to previous location » ?
How the code may know the original order ?
​
 
Dear Marc,

The code i have tried by recording Macro with added few lines is as below

Code: 
Sub Macro1()
'MOVE SHEETS THAT HAS PREFIX NAME STARTES WITH AP TO END OF THE SHEET
'BEFORE MOVING, NEXT SHEET NAME OF THE SHEET STARTS WITH AP SHALL BE SAVED TO USE IT LATER(in separate sheet)

' Macro1 Macro
'


For i = 1 To Sheets.Count
  If Left(Sheets(i).Name, 2) = "AP" Then



    Sheets("AP1Sheet3").Select 'Before moving to end, need to SAVE the
    'location of its AFTER Sheet no. to bring back SAY save  Sheets(4)
    Sheets("AP1Sheet3").Move After:=Sheets(9) 'last sheet number   'save  Sheets(2)
    Sheets("AP2Sheet5").Select
    Sheets("AP2Sheet5").Move After:=Sheets(9) 'last sheet number'
   
Next i
End Sub
Sub Macro2()
'REARRANGE BACK TO ORIGINAL LOCATION
' Macro2 Macro
'

'
    Sheets("AP1Sheet3").Select
    Sheets("AP1Sheet3").Move Before:=Sheets(5) 'PREVIOUS LOCATION Sheets(4)
    Sheets("AP2Sheet5").Select
    Sheets("AP2Sheet5").Move Before:=Sheets(1) 'PREVIOUS LOCATION Sheets(4)

End Sub
 

Crystal clear explain the issue …

As it can be done manually, just activate the Macro recorder
and well operate.
​
 
Dear Marc,

There are more than 200 sheets
Among 200 sheets, sheet name starting with AP first 2 letters is around 50
Manually we can move all these AP sheets to end, but bringing back to the original location where it is already there is not possible

Trying with CodeName of the sheet

Thanks
 
The first rearrange is easy, logic at child level, just use a reverse loop.

But to go back to initial order, what is your thinking, your way ?
(Differents ways, just think about it …)

Without any strategy, it's just an harcode way
you can easily achieve using the Macro Recorder …

According to your very little attachment, example of the harcode way
(just by reading VBA inner help) :

• moving after "Sheet10" :

Worksheets("AP3Sheet4").Move , Worksheets("Sheet10")

• Reverse, go back to previous order,
so just move before the sheet which was after it,
for this sheet it was "Sheet10" again (but not for others) :

Worksheets("AP3Sheet4").Move Worksheets("Sheet10")

Repeat same process for each sheet to move …
​
 
nagovind said:
Trying with CodeName of the sheet
Click to expand...
It's a good way - I yet have a code working with your tiny sample -
but only if you are able within your Excel version to activate the option
« Trust Access to the VBA Project object model » ?

If not, just think about another way (many easier than CodeName) …
​
 
Hi nagovind,

Why do you need to reorder the sheets? If it's because you don't want to "see" the "APsheets", you could hide them instead?
(just trying to understand your use case as their might be an alternative solution).

Otherwise, you could have a hidden sheet where you would store the order of the sheets (both possibilities) and an other macro that would refer to the information on the hidden sheet to reorder them.
 
You must log in or register to reply here.
Back
Top