several excel worksheets that I want to add but not use twice

[Bob Kempsell]

Hi Group, I am using a VBA to add a master Worksheet with the date as the name weekly.

in addition I have 20 worksheets that have a safety related topic I would like to use a VBA to add one of the safety topic worksheets weekly but do not want to use the same worksheet that has already been used.

Below is the VBA that I'm using to add the Master worksheet

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Master")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)

Dim SheetName As String

SheetName = Format(Date, "mm-dd-yyyy-""Definable Work Areas") 'Change the format as per your requirement

ActiveSheet.Name = SheetName

End Sub

Below is the VBA that I'm using to add the Safety Topic Worksheet

Sub Copysafetysheet()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Safety Topic 1")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)

Dim SheetName As String

SheetName = Format(Date, "mm-dd-yyyy-""Safety Brief") 'Change the format as per your requirement

ActiveSheet.Name = SheetName

End Sub

The Set ws1 = ThisWorkbook.Worksheets("Safety Topic 1") represents one of the Safety Topic Worksheets, there are 20 additionl sheets

Please help

 

[Luke M]

Sounds like we need some sort of list/tracking system to know which sheets have been copied. Could we perhaps count the number of sheets named "mm-dd-yyyy Safety Brief", and from that deduce how many Safety Topics have been copied? Or are there other Safety Brief sheets out there?

 

[Bob Kempsell]

The Sheets are named Safety Topic 1, Safety Topic 2 etc when they are copied the are renamed "mm-dd-yyyy"

Right now I'm trying to figure out the VBA that will allow a cell (ref on another worksheet)with a drop down be referenced to the sheet that i'd like to copy then rename that sheet as described above

 

[Luke M]

Example code:

Dim userChoice as Long
Dim shName as String
Dim ws as Worksheet

userChoice = Worksheets("Sheet1").Range("B1").Value
shName = "Safety Topic " & userChoice
Set ws = Worksheets(shName).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ws.Name = Format(Date, "mm-dd-yyyy-""Safety Brief")

 

[Bob Kempsell]

Luke I really appriciate your help but I'm getting this error, attached is the workbook

 

[Luke M]

Ah, I presumed that J11 had a number in it. I see now that it's pulling from another list. I put a formula near the SafetyTopics range, and now use that in the macro. I also added a bit of error checking, just in case.