Sort Numerically Worksheets via VB when creating a new worksheets

[John]

Is it possible to sort numerically worksheets via VB when you create a new
worksheet. I have the following code that creates a new worksheet and
renames it based on the value in cell A8 and places it after a sheet called
"Form", but how can I get this new sheet 'placed' numerically?

Thanks


Dim strSheetName As String ' Input Sheet Name

strSheetName = Format(Worksheets(ActiveSheet.Name).Range("a8"),
"dd-mm-yy")

Sheets.Add
ActiveSheet.Name = strSheetName
ActiveSheet.Select
ActiveSheet.Move After:=Sheets("Form")

 

[Bob Phillips]

What exactly does numerically mean?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

My example uses date format, so in this instance it would be chronological,
but eg the worksheets would be placed as such 31/05/04; 30/05/04; 29/05/05
etc

If my 'naming cell' was a numeric value sheets would be sorted 5;4;3;2;1 etc

 

[Bob Phillips]

John,

The sorting is relatively simple, but it would have to be tailored. By this
I mean that any code that sorted 5,4,3,2,1 into order would not work with
31/05/04; 30/05/04; 29/05/05 as it would not implicitly know the latter is
dates.

You would have to have specific code for the date to convert to a number, or
use a strict formay such as yyyy mm dd,l and then sort accordingly

Sub SortSheets()
Dim i As Long
Dim j As Long

For i = 1 To Worksheets.Count - 1
For j = i + 1 To Worksheets.Count
If Worksheets(i).Name > _
Worksheets(j).Name Then
Worksheets(i).Move after:=Worksheets(j)
End If
Next j
Next i

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

Thanks for the reply Bob. Yes my file will have a strict format type i.e.
date or numerical

I'm a bit lost as to where I should place your code. I place it as below but
things seemed to go all over the place. I wish to retain the worksheet
'Form' as the first worksheet i..e all subsequent worksheets to the right of
it, sorted


Dim lngPosY As Long ' Input Cell Number
Dim lngOutY As Long ' Output Cell Number
Dim strSheetName As String ' Input Sheet Name
Dim i As Long
Dim j As Long

strSheetName = Format(Worksheets(ActiveSheet.Name).Range("a8"),
"dd-mm-yy")

Sheets.Add
ActiveSheet.Name = strSheetName
ActiveSheet.Select


For i = 1 To Worksheets.Count - 1
For j = i + 1 To Worksheets.Count
If Worksheets(i).Name > _
Worksheets(j).Name Then
Worksheets(i).Move after:=Worksheets(j)
End If
Next j
Next i

 

[Bob Phillips]

John,

The code would just go in a macro in a standard code module.

To keep Form at the front, start at worksheet 2

For i = 2 To Worksheets.Count - 1

But this won't worki with your date format. Take these 3 dates
1 20-02-04
2 30-11-03
3 31-01-03

They should sort as 3,2,1 but will sort as 1,2,3. You have to use a date
format where year comes first, then month, then day.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[John]

Thanks Bob

John,

The code would just go in a macro in a standard code module.

To keep Form at the front, start at worksheet 2

For i = 2 To Worksheets.Count - 1

But this won't worki with your date format. Take these 3 dates
1 20-02-04
2 30-11-03
3 31-01-03

They should sort as 3,2,1 but will sort as 1,2,3. You have to use a date
format where year comes first, then month, then day.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

right latter