Inserting a new sheet alphabetically but start at a certain sheet?

[Apollo6]

I added this thread a while back and it is working fine. However, I am now faced with a twist to the original issue of inserting a new sheet alphabetically.

I have a workbook that, for example, has 22 sheets in it. The first sheet(1) is a Total sheet for the following 10 sheets(doctors). Sheet(12) is another Total sheet for the remaining 10 sheets(hospitals).

I have created an automated process that a user can add a new doctor sheet and it is placed alphabetically from sheet(2), I have coded it to ignore the first sheet. This method works fine for the doctors who's section in the workbook comes first. However, when adding a new hospital sheet, the code places the new hospital alphabetically with the doctors.

If I know the name of the Total sheet for the hospitals, how can I force the insert to start at the Total sheet for the hospitals and start looking to place the new sheet?

Currently, I am using the following code:

Dim oSheet as Object
Dim strNewLocation as String
Dim strAfter as String

For Each oSheet In Application.Workbooks("Totals.xls&quot.Worksheets
If oSheet.Name = "TOTALPHYSICIAN" Then
GoTo ERR_SKIP_SHEET
ElseIf oSheet.Name > UCase(strNewLocation) Then
oSheet.Activate
strAfter = oSheet.Name
Exit For
End If
ERR_SKIP_SHEET:
Next oSheet
Sheets(strNewLocation).Move Before:=Sheets(strAfter)

Any ideas are appreciated.

 

[segmentationfault]

I think the simplest solution would be to use a separate macro that is identical to your existing one except that instead of "TOTALPHYSICIAN" you have "TOTALHOSPITAL", or whatever particular name you need. Then you have a macro that inserts a new doctor sheet in alphabetical order and one that inserts a new hospital sheet in alphabetical order.

If that is an unlikable solution, you will need some means of determining whether the new sheet is a doctor or a hospital. You can achieve this any number of ways, possibly even by examining the name of the new sheet (for example, every doctor sheet has a comma, as in "Smith, John", while no hospital names have commas). You could also use a form with an option box, button, et cetera.

I do think the first method, with two separate macros, would be the most effective solution, but perhaps there is more to the situation than the description reveals.

 

[Apollo6]

Thanks for the reply...

There are actually two separate macros depending on what you want to add. No matter what macro is ran, the first sheet in the workbook will be 'TOTALPHYSICIANS'.

I am not able to change the doctor's names or hospitals. Neither of which contains any unique characters.

I am thinking there needs to be a different way to loop through the sheets without using the 'For Each' method. That is what is forcing it to look left to right.

Still thinking...

 

[Apollo6]

Just to post my solution, what I got to work in this situation:

Knowing the name of the Total sheet that I would need to start checking where to put a new location sheet, I move the Total sheet(Locations) index into a variable and test for that condition in my 'For Each' loop. If the current sheet index is <= than my variable, skip the sheet, else start using the alpha logic.

 

[segmentationfault]

Sorry, I glossed over your code the first time. I thought you were running this macro when you wanted to add a sheet. Your existing code seems to sort all of the sheets into the proper order. That is a better approach.

Try this code. It should work until you have more than 50 sheets in each group (physicians, hospitals) to sort. At that point it will function, but will just perform slowly.

If you have problems with it, I'll try to iron them out.

Public Sub TwoTierAlphabeticOrder()
    ' This macro will sort the sheets of the active
    ' worksheet into a two tier case insensitive
    ' alphabetic order. The tiers are designated by
    ' header sheets. An example of the sorted workbook
    ' is given.
    '
    ' Example:
    ' HeaderOne, C, E, A, HeaderTwo, D, B, F
    ' becomes
    ' HeaderOne, A, C, E, HeaderTwo, B, D, F
    '
    ' The assumption is made that all the sheets are
    ' in the correct group but unsorted.
    
    ' The names of the header sheets
    Const strHeaderOne As String = "TOTALPHYSICIANS"
    Const strHeaderTwo As String = "TOTALHOSPITALS"
    
    ' Put strHeaderOne as the first sheet in the book
    Worksheets(strHeaderOne).Move before:=Worksheets(1)
    
    ' Remember the location of the strHeaderTwo sheet
    Dim intPosHeaderTwo As Integer
    intPosHeaderTwo = Worksheets(strHeaderTwo).Index
    
    ' Used to perform a bubble sort
    Dim i As Integer
    Dim j As Integer
        
    ' Make sure there is something to sort
    If intPosHeaderTwo > 2 Then
        
        ' Bubble sort everything up to header two into case insensitive
        ' alphabetic order
        For i = 2 To intPosHeaderTwo - 1
            j = i + 1
            While UCase(Worksheets(j).Name) < UCase(Worksheets(j - 1).Name) And j > 2
                Worksheets(j).Move before:=Worksheets(j - 1)
                j = j - 1
            Wend
        Next i
    End If
    
    ' Makes sure that there is something to sort after the second header
    If Worksheets.Count >= intPosHeaderTwo + 1 Then
        
        ' And bubble sorts everything after the second header into case
        ' insensitive alphabetic order
        For i = intPosHeaderTwo + 1 To Worksheets.Count - 1
            j = i + 1
            While UCase(Worksheets(j).Name) < UCase(Worksheets(j - 1).Name) And j > intPosHeaderTwo + 1
                Worksheets(j).Move before:=Worksheets(j - 1)
                j = j - 1
            Wend
        Next i
    End If
End Sub

 

[segmentationfault]

Hm, please be aware that the lines &quot;j > 2&quot; and &quot;j > intPosHeaderTwo + 1&quot; are part of the previous lines and will happily generate errors if they are left as they appear.

 

[Apollo6]

To answer your reply, this situation is for adding new doctors or locations into an existing workbook that the first half are physicians and the second half are locations with a Locations Total sheet dividing the two groups. This code will look through every sheet in the workbook and compare it's sheet index with an identified sheet(Locations Total). It will skip all sheets until if finds the Locations Total sheet the from that point start checking to see if it goes alphbetically. Note: The existing sheets are already alph to start within their own groups(Physicians and Locations).

Thanks for suggestions!!!

Here is the code:

Dim oSheet as Object
Dim strCur_File as String
Dim strNewLoc as String
Dim iSheetIndex as Integer
Dim strAfter as String


For Each oSheet In Application.Workbooks(strCur_File).Worksheets
If oSheet.Name = strNewLoc Then
GoTo ERR_SKIP_SHEET
ElseIf oSheet.Index <= iSheetIndex Then
GoTo ERR_SKIP_SHEET
ElseIf oSheet.Name > UCase(strNewLoc) Then
oSheet.Activate
strAfter = oSheet.Name
Exit For
End If
ERR_SKIP_SHEET:
Next oSheet
Sheets(strNewLoc).Move Before:=Sheets(strAfter)