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

[Excel Fox]

just write


If wks.Name <> "Sheet1" And ((wks.Cells(5, 3).Value = rng.Cells(lng).Value) OR (wks.Cells(5, 2).Value = rng.Cells(lng).Value)) Then

[naveenroy]

Hi,

Thanks so much for your help everyone appreciated for this sheet thanks for your patience and your great help.

I know am asking to much this last modification i need.. like they need summary sheet.I have wrote a macro but iam not able to get all the fields I have attached in the link below.

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

In sheet1 .. D1 to I1 i have mentioned cell names for all sheets which i wanted in the summary sheet. i also want which are in blank and color and format also.

Thanks a lot this will be last help. Please

[naveenroy]

Hi,

Thanks so much for your help everyone appreciated for this sheet thanks for your patience and for your great help.

I know am asking to much this last modification i need.. Last thing i need is summary sheet.I have wrote a macro but iam not able to get all the fields I have attached in the link below.

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

In sheet1 .. D1 to I1 i have mentioned cell names for all sheets which i wanted in the summary sheet. i also want which are in blank in cell, same color and format also and i need Hyperlink to the Proposal ID in summary when clicked it wil do tht particular sheet.

Thanks a lot this will be last help. Please

[naveenroy]

Hi Sir,

Please help in fix this code for summary data where i need particular/some cell values in each sheet and hyper link the proposal ID

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