Keep specific worksheets in a particular order within workbook

[Jak]

Hi all

Just a quick question on worksheet order. Is there a way to keep specific worksheet in a particular order via VBA.

Example.

Sheet1, Sheet2, Returns, Log, SLAs

In a shared workbook, the worksheet names: "Returns", "Log", "SLAs" need to be the last three worksheets on the right hand side of the workbook. When users add additional worksheets, this changes the ordering ie.

Sheet1, Sheet2, Returns, Log, SLAs, Sheet4

Is it possible to search for these sheet names and move them as outlined above when the workbook is opened?

 

[JamesW]

Hi,

Check this out, might be of some use to you:

http://www.cpearson.com/excel/sortws.aspx

 

[doofusboy]

Here's one way. You could also do in Workbook_BeforeClose or Workbook_BeforeSave.

Private Sub Workbook_Open()
    Sheets("Returns").Move After:=Sheets(Worksheets.Count)
    Sheets("Log").Move After:=Sheets(Worksheets.Count)
    Sheets("SLAs").Move After:=Sheets(Worksheets.Count)
End Sub

 

[Jak]

Hi JamesW and doofusboy

Thanks for your help on this. Greatly appreciated.

Jak