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.

 

[Joe4]

Like this:

Sheets(4).Activate

Since the 4 does not have quotes around it, it does not think that is the sheet's name. Rather, it treats it as an index (the 4th sheet in the workbook).

 

[yinkajewole]

i don't understand the code.

I have (Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6, Sheet7, Sheet8, Sheet9, Sheet10)
Then I want to move (Sheet4, Sheet5, Sheet6) to be after (Sheet8)

 

[Joe4]

Oh sheesh, I really misread that question. Sorry about that.

Actually, this is an example of one that you can very easily get the code yourself, without having to know or write a single line of VBA code.
Simply turn on the Macro Recorder, and record yourself doing it manually, and you will have the code that you need.
You can delete the "Select" lines, as it really isn't necessary to physically select the sheets to move them.
So that would just leave you with:

Sub MyMoveSheets()
    Sheets("Sheet4").Move Before:=Sheets(9)
    Sheets("Sheet5").Move Before:=Sheets(9)
    Sheets("Sheet6").Move Before:=Sheets(9)
End Sub

The Macro Recorder is a great tool to get little code snippets like this!

 

[yinkajewole]

Oh sheesh, I really misread that question. Sorry about that.

Actually, this is an example of one that you can very easily get the code yourself, without having to know or write a single line of VBA code.
Simply turn on the Macro Recorder, and record yourself doing it manually, and you will have the code that you need.
You can delete the "Select" lines, as it really isn't necessary to physically select the sheets to move them.
So that would just leave you with:

Sub MyMoveSheets()
    Sheets("Sheet4").Move Before:=Sheets(9)
    Sheets("Sheet5").Move Before:=Sheets(9)
    Sheets("Sheet6").Move Before:=Sheets(9)
End Sub

The Macro Recorder is a great tool to get little code snippets like this!

Thanks. I know as much as that. Actually, the problem is that I want to make it with a Userform, hence, I don't know to compose the code so that I can move any sheet or sheets I want in any workbook.

 

[Joe4]

Thanks. I know as much as that. Actually, the problem is that I want to make it with a Userform, hence, I don't know to compose the code so that I can move any sheet or sheets I want in any workbook.

OK, I may have misread your original question, but I know that wasn't part of it!

Are you asking to make something interactive, so when run, it prompts the user for which sheets they want to move and to where?
Personally, I avoid using User Forms in Excel, as I find them rather clunky to program. For simple things, I will just use Input Boxes in my VBA code to prompt the users for Input.
If I have a more complex form, I typically lean towards using Access, which is much easier to create forms in, as you can easily "bind" the fields on the Form to fields in a Table (as opposed to Excel, in which you typically need to explicitly link everything in VBA code).

It doesn't look like you would be mapping anything to Excel, but if my assumption is right, it seems like it should be easy enough to do without having to create User Forms.
If you are good with Input Boxes, I can probably help with that. But just want to be sure you provide all the details/restrictions/limitations you want up front, to make sure it is a feasible way to go.

 

[yinkajewole]

OK, I may have misread your original question, but I know that wasn't part of it!

Are you asking to make something interactive, so when run, it prompts the user for which sheets they want to move and to where?
Personally, I avoid using User Forms in Excel, as I find them rather clunky to program. For simple things, I will just use Input Boxes in my VBA code to prompt the users for Input.
If I have a more complex form, I typically lean towards using Access, which is much easier to create forms in, as you can easily "bind" the fields on the Form to fields in a Table (as opposed to Excel, in which you typically need to explicitly link everything in VBA code).

It doesn't look like you would be mapping anything to Excel, but if my assumption is right, it seems like it should be easy enough to do without having to create User Forms.
If you are good with Input Boxes, I can probably help with that. But just want to be sure you provide all the details/restrictions/limitations you want up front, to make sure it is a feasible way to go.

In that case, how do i use Input Boxes in VBA code to prompt the users for Input ?

 

[Joe4]

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

 

[My Aswer Is This]

I use UserForms all the time and love them. I have UserForm in my Personal Folder so they can be used on any Workbook.
So give me a example of how you want to do this with a Userform.

You would have to give the script the Sheet to move and move before or after what Sheet.

Put this script in a Button on your Userform

This script uses Two Userform Textboxes

To work on any Workbook you would need to install your UserForm
In your Personal Workbook.

Open The Userform using another script installed in your Personal Folder by using a Shortcut key or a button on your Custom built Ribbon.

Here is my example:

Enter a sheet number in Textbox1 and Textbox2​

Sheet Number like 1 or 5

Not sheet name.

Private Sub CommandButton1_Click()
'Modified  4/9/2019  1:58:13 PM  EDT
Dim One As Long
Dim Two As Long
One = TextBox1.Value
Two = TextBox2.Value
Sheets(One).Move After:=Sheets(Two)
End Sub

 

[yinkajewole]

I use UserForms all the time and love them. I have UserForm in my Personal Folder so they can be used on any Workbook.
So give me a example of how you want to do this with a Userform.

You would have to give the script the Sheet to move and move before or after what Sheet.

Put this script in a Button on your Userform

This script uses Two Userform Textboxes

To work on any Workbook you would need to install your UserForm
In your Personal Workbook.

Open The Userform using another script installed in your Personal Folder by using a Shortcut key or a button on your Custom built Ribbon.

Here is my example:

Enter a sheet number in Textbox1 and Textbox2​

Sheet Number like 1 or 5

Not sheet name.

Private Sub CommandButton1_Click()
'Modified  4/9/2019  1:58:13 PM  EDT
Dim One As Long
Dim Two As Long
One = TextBox1.Value
Two = TextBox2.Value
Sheets(One).Move After:=Sheets(Two)
End Sub

your code works for one sheet at a time. I really need the one that works with multiple sheets