Sort Excel Sheets In Custom Order Based On Value In A List

[naveenroy]

Hi Everyone,

I have workbook which has 40 sheets, i want to place/move all the sheets in order as per the list of value in sheet1.
Note: Sheet names are different . i want to move the sheets from the value you place in sheet1, the value will be there in some sheet. So that sheet should move as per the list in sheet1

EG:
In Sheet 1 -- i am placing value in B2 column 20, 10, 30. And there are Three sheets named as A, B, C. In Sheet "A" in cell B2 the value 10 is there, In "B" sheet contain a value 20 in b2, In sheet "C" , B2 contain 30.
When i place 20,10,30 in sheet1, the sheet which cotains the values need to moved as per the list.


Can you please help to wirte macro or VB code.

[Excel Fox]

Can be done naveenroy. But just to be sure exactly how you want the output, can you post a sample file that has the expected output, after macro will be run.

[naveenroy]

Hi ,

Thanks for the quick reply. I need an out put like this.

Basically i have 35 sheets. In Every sheet at B2 cell there Project ID. I want to get all the project Id's of all sheets in the sheet1 of B column . And then in C Column i will be Prioritize like 1,2,3 etc and move the sheets as per priority.

So on click of button a need all Projects in Sheet1 of B column, after i prioritize in C column (1,2,3) Sheets need to moved as prioirtize

Example:
Sheet1:
B C
100 2
200 1
500 3

Note : Sheets names are Project's Names

** Iam not able to attach the excel. let me know how to attach tht excel

[naveenroy]

Need another automation which is very similar to the above .

I have 35 worksheets. When i put the Projects ID's in B2 to b36 (List) of Sheet1. (The projects ID will be there in any of 35 sheets) After finding projects ID in any sheet that sheet need to moved as per the list in the workbook.

Note: All sheets are names as Project name's

[Excel Fox]

To attach a file, please go to the advanced edit options. The scroll down a bit, and you'll find the attachment button. You can upload a file within certain size limits. If the file size is beyond the forum limit, you can upload the file to any fileshare sites, and then post a link here.

[naveenroy]

Please find the attachment.

As in the sheet1 there are value which will be there in any of the sheets A or B or C sheet.
eg: So output should be in sheet1 20,10,30(which i will be puting the value in any order) so in First place/move the sheet which contain 20 to be first sheet and secong sheet which contain 10 and so on..

[Excel Fox]

Try this

Sub MoveSheetAround()

    Dim lng As Long
    Dim wks As Worksheet
    Dim lngCrVlu As Long
    Dim rng As Range
    With Worksheets("Sheet1")
        Set rng = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
    End With
    For lng = 1 To rng.Rows.Count
        For Each wks In ThisWorkbook.Worksheets
            If wks.Name <> "Sheet1" And wks.Cells(2, 2).Value = rng.Cells(lng).Value Then
                wks.Move After:=ThisWorkbook.Sheets(lng + 1)
                Exit For
            End If
        Next wks
    Next lng
    
End Sub

[naveenroy]

Thanks So much for the code. Its running in tht sheet
But i am not able to run in my actual sheet of mine plz find the sheet in the link

https://www.box.com/s/mkhnxvfj96a8kx6lh3e2

[bakerman]

Sub MoveSheetAround()

    Dim lng As Long
    Dim wks As Worksheet
    Dim lngCrVlu As Long
    Dim rng As Range
    With Worksheets("Sheet1")
        Set rng = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
    End With
    For lng = 1 To rng.Rows.Count
        For Each wks In ThisWorkbook.Worksheets
            If wks.Name <> "Sheet1" And wks.Cells(5, 3).Value = rng.Cells(lng).Value Then
                wks.Move After:=ThisWorkbook.Sheets(lng + 2)
                Exit For
            End If
        Next wks
    Next lng
    
End Sub

[naveenroy]

Thanks so much Simply Great!! Thanks so much for your help.
Just small Clarification

In the code :

in If condition it refers to C5, i also want to refer B5 if that number is in the list.



https://www.box.com/s/bs98wqyaw3ahvv348nc1