Modify Macro to have sheets added in particular order

[BReilly]

Tender - Blank - Revision4.xlsmHello,

I have a Marco built to add copies of a template sheet and rename them based on a predefined list. if i add values to this list the macro will create and rename the sheet to match the list, the issue is that the new sheets created are placed at the end of the list of sheets and i want it to place the newly created sheets in the proper sequence based on the original pre-defined list.

I have attached the workbook for reference.
https://1drv.ms/f/s!AhOE4M08YrXigRM78YnhByxmqSZh

the code in question is below;

Option Explicit


Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range


With ThisWorkbook                                               'keep focus in this workbook
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    Set wsMASTER = .Sheets("Tender Form")                       'sheet with names
                                                                'range to find names to be checked
    Set shNAMES = wsMASTER.Range("A12:A" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
    
    Application.ScreenUpdating = False                              'speed up macro
    For Each Nm In shNAMES                                          'check one name at a time
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then   'if sheet does not exist...
            wsTEMP.Copy after:=.Sheets(.Sheets.Count)               '...create it from template
            ActiveSheet.Name = CStr(Nm.Text)                        '...rename it
        End If
    Next Nm
    
    Application.ScreenUpdating = True                           'update screen one time at the end
End With


MsgBox "All sheets created"
End Sub

your help is greatly appreciated!

[Paul_Hossler]

1. I added CODE tags around your macro -- you can use the [#] icon the next time and paste your macro between them

2.

I have attached the workbook for reference.

https://1drv.ms/f/s!AhOE4M08YrXigRM78YnhByxmqSZh

You should attach a workbook to your post -- bottom right click [Go Advanced] and use the paperclip icon. #2 in my sig has more information

[BReilly]

I have updated the Post to have the File attached.

[Fluff]

Try

    i = 2
    For Each Nm In shNAMES
         i = i + 1
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then
            wsTEMP.Copy after:=.Sheets(i)
            ActiveSheet.name = CStr(Nm.Text)
        End If
    Next Nm

[BReilly]

where does it get entered in the Marco?

[Fluff]

Like this

Sub SheetsFromTemplate()
Dim wsMASTER As Worksheet, wsTEMP As Worksheet, wasVISIBLE As Boolean
Dim shNAMES As Range, Nm As Range
Dim i As Long

With ThisWorkbook                                               'keep focus in this workbook
    Set wsTEMP = .Sheets("Template")                            'sheet to be copied
    Set wsMASTER = .Sheets("Tender Form")                       'sheet with names
                                                                'range to find names to be checked
    Set shNAMES = wsMASTER.Range("A12:A" & Rows.Count).SpecialCells(xlConstants)     'or xlFormulas
    
    Application.ScreenUpdating = False                              'speed up macro
    i = 2
    For Each Nm In shNAMES
         i = i + 1
        If Not Evaluate("ISREF('" & CStr(Nm.Text) & "'!A1)") Then
            wsTEMP.Copy after:=.Sheets(i)
            ActiveSheet.name = CStr(Nm.Text)
        End If
    Next Nm
    
    Application.ScreenUpdating = True                           'update screen one time at the end
End With


MsgBox "All sheets created"
End Sub

[BReilly]

if i add sheets in before the tender sheet it does not put them after the tender sheet, how can i fix this so that they are always put in order after the "Tender Sheet"

[Fluff]

I'm afraid I don't understand.
Does the code I supplied work?

[BReilly]

yes it works the only issue is that when it runs it doesn't put the new sheets at the end of the list of sheets, but it only does this if i add new sheets before the "Tender Form" that are not "Marco created Sheets"

[Fluff]

When I run it on your test file all the sheets are added after the "Tender Form" in the order they appear on the "Tender Form".

[BReilly]

yes that is correct, but add in a blank sheet before the tender form and it doesn't work for me it adds it them as the 3rd sheet in the list .

[Fluff]

In that case change the value in red on this line

 i = 2

to 3

[BReilly]

is there anyway to make the created sheets go after a reference sheet ie. "Tender Form" instead of a sheet number (i = "Tender Form" instead of i=2 or3)

[Fluff]

Yup, try

    i = .Sheets("Tender Form").Index - 1

[BReilly]

works flawlessly, thank you very much for your help!

[Fluff]

Glad to help & thanks for the feedback