BReilly
07-30-2018, 07:28 AM
22648Hello,
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!
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!