PDA

View Full Version : Modify Macro to have sheets added in particular order



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!

Paul_Hossler
07-30-2018, 08:07 AM
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
07-30-2018, 08:59 AM
I have updated the Post to have the File attached.

Fluff
07-30-2018, 10:58 AM
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
07-30-2018, 11:10 AM
where does it get entered in the Marco?

Fluff
07-30-2018, 11:36 AM
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
07-31-2018, 06:06 AM
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
07-31-2018, 06:15 AM
I'm afraid I don't understand.
Does the code I supplied work?

BReilly
07-31-2018, 06:22 AM
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
07-31-2018, 06:56 AM
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
07-31-2018, 07:07 AM
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
07-31-2018, 07:27 AM
In that case change the value in red on this line
i = 2to 3

BReilly
07-31-2018, 12:59 PM
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
07-31-2018, 01:16 PM
Yup, try
i = .Sheets("Tender Form").Index - 1

BReilly
07-31-2018, 01:43 PM
works flawlessly, thank you very much for your help!

Fluff
07-31-2018, 01:53 PM
Glad to help & thanks for the feedback