Sorting Excel Sheets and Charts

[tbg130]

Hi All,

I have a project that exports data from Access to Excel then creates charts as 'Worksheets' or Full Page Charts and my sort worksheet routine no longer works... It worked fine with just worksheets, but when added the 'charts' it started failing...

The error is 'subscript out of range'. It errors on 'w.Move Before:=objbook.Worksheets(w.Index)' line...

Any ideas on what could be wrong would be much appreciated.

Code:

Public Sub WorkSheetSort()

On Error GoTo SkipSort

For Each wnull In objbook.Worksheets
prevname = ""
For Each w In objbook.Worksheets
If prevname <> "" Then
If w.Name < prevname Then
w.Move Before:=objbook.Worksheets(w.Index)
Else
prevname = w.Name
End If
Else
prevname = w.Name
End If
Next
Next
SkipSort:

End Sub

Thanks everyone!

Tyler

 

[SkipVought]

Tyler,

Use Sheets instead of Worksheets

Public Sub WorkSheetSort()

    On Error GoTo SkipSort
    
    For Each wnull In objbook.Sheets
    prevname = ""
    For Each w In objbook.Sheets
        If prevname <> "" Then
            If w.Name < prevname Then
                w.Move Before:=objbook.Sheets(w.Index)
            Else
                prevname = w.Name
            End If
        Else
            prevname = w.Name
        End If
    Next
    Next
SkipSort:

End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[Loomah]

Tyler
Obviously Skip has given the solution you were looking for but, just for the hell of it, I had a look at the code you started with.

It didn't work for me, didn't sort the sheets at all!
Shouldn't the line
w.Move Before:=objbook.Sheets(w.Index)
be
w.Move Before:=objbook.Sheets(wnull.Index)
??????

Or is it just me (again!)?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​