Sort Numerically Worksheets via VB when creating a new worksheets
 

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")

Sort Numerically Worksheets via VB when creating a new worksheets
 

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" wrote in message
...
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")

Sort Numerically Worksheets via VB when creating a new worksheets
 

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" wrote in message
...
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" wrote in message
...
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")

Sort Numerically Worksheets via VB when creating a new worksheets
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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")

Sort Numerically Worksheets via VB when creating a new worksheets
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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")

Sort Numerically Worksheets via VB when creating a new worksheets
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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")

Sort Numerically Worksheets via VB when creating a new worksheets
 

Thanks Bob


"Bob Phillips" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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")