Dynamically inserting a worksheet using After:=

Hi guys,

I'm having trouble getting a worksheet to copy after the sheet I want.

    'loop through the sheets we need to be in the workbook - according to how many divisions there are
    For i = 1 To depts 'loop from 2 as 1 already exists, to the max number of departments
    
        'check to see if the worksheet already exists
        wsMax = Sheets.Count 'count the number of sheets in the workbook

        'loop through the worksheets
        For m = 1 To wsMax
            If Sheets(m).Name = i Then 'if we find the name of a worksheet is equal to our outer loop number ie the sheet we want to create
                exist = True 'we set a variable to true to show it already exists
                Exit For 'exit the loop as we have no need to continue looking for the sheet
            Else
                exist = False
            End If 'the default of the exist variable has already been set to false so no else statement is required
                       
        Next m 'continue loop

        If exist = False Then 'if the sheet does not already exists we want to create it
            Sheets("div").Copy after:=Worksheets(stname).Name 'copy the selected worksheet after the last one
            Sheets("div (2)").Visible = xlSheetVisible 'the sheet we are copying from is hidden so the copy is hidden. This makes it visible
            Sheets("div (2)").Select 'we are always going to copy tab 1 so we can hard code it (as Excel doesn't allow sheets to be called the same, it will copy the name and add (2)
            Sheets("div (2)").Name = i 'rename the copied sheet to the relevant number
        End If
    
        stname = Sheets(i).Name 'name of previous sheet

    Next i 'continue the loop

I have a number of worksheets (currently numbered 1 to 10) as well as some others called various different things - the important thing is that the worksheet named 1 is not the first sheet in the workbook (and I don't want to hard code it as it may change).

My problem is that when I create a worksheet called 4, it is being inserted after the 3rd worksheet, not the worksheet called 3.

Hope that makes a bit of sense!!

I think it's this line that is the problem:

Sheets("div").Copy after:=Worksheets(stname)

Ideally I would like to use the codename property but I can't seem to make that work.

Thanks in advance
Sandra

Sandra

Are you using numbers, eg 1,2,3 etc, to name the sheets?

If you are that could be causing a conflict btween sheet index and sheet name.

For example in code if you had Sheets(3) it would refer to the sheet with index 3, not necessarily the sheet with the name '3'.

To ensure you are referring to the sheet with the name 3 use CStr.

Dim I As Long

    I = 3

    MsgBox Sheets(I).Name

    MsgBox Sheets(CStr(I)).Name