Move specific sheets

[yinkajewole]

Assuming I have 10 sheets in a workbook, how do i move the 4th to 6th sheet to sheet 8 using vba.

 

[yinkajewole]

Try something like this:

Sub MyMoveSheets()

    Dim input1 As Integer
    Dim input2 As Integer
    Dim input3 As Integer
    Dim i As Integer
    
'   Prompt to input sheets to move
    input1 = InputBox("Which sheet index is the first you would like to move?")
    input2 = InputBox("Which sheet index is the last you would like to move?")
    
'   Check to see that the last number is after the first number
    If input2 < input1 Then
        MsgBox "The last sheet number to move must be greater than the first to move!", vbOKOnly, "ERROR! TRY AGAIN!"
        Exit Sub
    End If

'   Prompt to ask where to move sheets to
    input3 = InputBox("Where sheet index would you like to move them before?")
    
'   Check to see if destination sheet exists
    If input3 > Sheets.Count Then
        MsgBox "You entered a sheet number that exceeds the number of sheets in the workbook!", vbOKOnly, "ERROR! TRY AGAIN!"
        Exit Sub
    End If
    
'   Move sheets
    For i = input1 To input2
        Sheets(input1).Move Before:=Sheets(input3)
    Next i
    
End Sub

from your code this is what I can deduce from it.

Dim i As IntegerFor i = 2 To 4
Sheets(i).move Before:=Sheet5
Next i

It goes in line with what I want but the problem is that it does not move the sheets in order.

 

[Joe4]

I am not sure what you are asking.

Please provide me with an actual example. Tell me what you are entering for the three prompts, what is happening, and what you are expecting/wanting to happen.

 

[yinkajewole]

I am not sure what you are asking.

Please provide me with an actual example. Tell me what you are entering for the three prompts, what is happening, and what you are expecting/wanting to happen.

I entered 2, 4, and 1. That is it should move Sheet2, Sheet3, Sheet4, before Sheet1
It moved only Sheet2, while the rest (Sheet3 and Sheet4) still remained in their position i.e (Sheet2, Sheet1, Sheet3, Sheet4, Sheet5).
Actually, I wanted it to be like this (Sheet2, Sheet3, Sheet4, Sheet1, Sheet5).
I hope you understand better...

 

[Joe4]

Hmmm...

It seems to get a little tricky, as you move the sheets, the order is changing, and it depends on whether you are moving them backwards or forwards.
Before I spend a lot of time trying to figure something out, I am just curious about the need for it. It isn't that hard to click-and-drag sheets to move them. So it seems that this might only save a few seconds of time, at best.
How big is the need for this, and how are you viewing on using this?

 

[yinkajewole]

Hmmm...

It seems to get a little tricky, as you move the sheets, the order is changing, and it depends on whether you are moving them backwards or forwards.
Before I spend a lot of time trying to figure something out, I am just curious about the need for it. It isn't that hard to click-and-drag sheets to move them. So it seems that this might only save a few seconds of time, at best.
How big is the need for this, and how are you viewing on using this?

yes, you are right click-and-drag is easy and quick but when it comes to large number of sheets using the mouse can sometimes be stressful, in the process of moving it, the mouse can just release the click at times, hence, a macro will easily save me the stress

 

[Joe4]

Because sheet indexes are relative, and we are moving them around (which is changing the indexes as we are running the macro), I cannot think up of a good way to do it that isn't tricky or complex (there may be, I just don't know of a good way to do it).

If it was just moving one sheet at a time, it would be pretty straightforward.

 

[My Aswer Is This]

And my script does that. But the user does not want to do this three times. I have no ideal how to do this all at once.

Because sheet indexes are relative, and we are moving them around (which is changing the indexes as we are running the macro), I cannot think up of a good way to do it that isn't tricky or complex (there may be, I just don't know of a good way to do it).

If it was just moving one sheet at a time, it would be pretty straightforward.

 

[yinkajewole]

well... i will be troubleshooting it if I can make a headway

 

[Joe4]

I have no ideal how to do this all at once.

I'm glad I am not the only one who finds this rather difficult.

yinkajewole,
Maybe we should take a step back and see if we can come at this from a different angle. Maybe it is a design issue that can be resolved in a different manner.
Can you explain why are you having to move so many sheets around? How do they end up in the wrong order in the first place?
Maybe there is something we can do there.

 

[yinkajewole]

This is the best code working for it
<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">

Sub Move()
Dim i As Integer
    For i = 4 To 6
        Sheets(4).Move before:=Sheets(8)
    Next i
End Sub

Many thanks for your contributions</code>