Move WorkSheets Within Same WorkBook!!

[James Cooper]

Hello,

I've created a workbook with 80 worksheets. Now I have added 40 worksheets.
The problem is I need to move the added worksheets but I can't use the work
sheet names to move the worksheets with the following code

Sub ORGANIZE_WORKBOOK_SHEETS()
Sheets("CHICOPEE Q").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-11
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("CHICOPEE M").Select
Sheets("CHICOPEE M").Move Before:=Sheets(69)
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("HODGES OIL BUILDING M").Select
Sheets("HODGES OIL BUILDING M").Move Before:=Sheets(71)
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("HODGES OIL BLDG Q").Select
End Sub

Example:
The order of the work sheets are not alphabetical!
They are: Chicopee Q, Chicopee BW, Hodges Oil Building Q, Hodges Oil
Building BW, Visual Art Q, Visual Art BW, Baldwin Hall Q, Baldwin Hall BW,
Main Library Q, Main Library BW

Now when I added my current worksheets they are not in the order I need
them.
They are: Chipcopee M, Hodges Oil Building M, Visual Art M, Baldwin Hall M,
Main Library M

The Name of my worksheet is in B2 and B7 and the worksheet tab has the
worksheet name also.

This is what I want to do is move tme Buildings to look like this: Chicopee
Q, Chicopee M, Chicopee BW, Hodges Oil Building Q, Hodges Oil Building M,
Hodges Oil Building BW, Visual Art Q, Visual Art M, Visual Art BW, Baldwin
Hall Q, Baldwin Hall M, Baldwin Hall BW, Main Library Q, Main Library M,
Main Library BW

How can I move the work sheets in a workbook from one location to another
specific location?

Thank you for your help in advance!!!
James Cooper

 

[jseven]

I would do it like this:
Pull all worksheet names into one worksheet. Let's say on Sheet 1 in
column B.
Sub FindWSNames()
For x = 1 To Sheets.Count
Cells(x, "B") = Sheets(x).Name
Next x
End Sub

Then sort that list anyway you want, and run this code against it.
Sub SortWS()
On Error Resume Next
With Sheets("Sheet1")
For x = 1 To 16 'EDIT TO ACCOUNT FOR YOUR VARIABLES
Sheets(.Cells(x, "B").Text).Move After:=Sheets(Sheets.Count)
Next x
End With

End Sub

Tested it and it works pretty fine.

Regards,
Jamie

 

[jamesfc30]

Hello jseven,

Thank you for the code it worked great and will save me a lot of time!

Thank you for your help,
James Cooper

 

[jseven]

fantastic