Sort Numerically Worksheets via VB when creating a new worksheets

  • Thread starter Thread starter John
  • Start date Start date
J

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


Bob Phillips said:
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
Click to expand...
 
Back
Top