Adding sheets from a list

[Brutium]

Hello All,
I have a macro which creates worksheets based on a list in a specific sheet that I called Tools. The macro looks at this list and creates sheets which are named according to the list. What I need to accomplish now, is to have additional sheets created if and when I need to update or add to my list without interfering with the already created sheets. Is this doable? Please note that all the sheets that were created, report to a Summary sheet, and the new sheets that would be created will need to report to the Summary sheet as well.

Do I make sense?

Could anyone steer me in the right direction.
Any help would be greatly appreciated.
Brutium

 

[JoeMo]

Can you post your code?

 

[Brutium]

Hello JoeMo,
this is the code I use to create the various sheets.

Sub Addsheets()
Dim LR As Long, i As Long, MasterSheet As Long
Dim rngTopOfList As Range
Dim wsName As String
Dim n As Integer, j As Integer, x As Integer

n = 0
With Sheets("tools")
ActiveSheet.Shapes(Application.Caller).Visible = False

Set rngTopOfList = .Range("B3")

LR = .Cells(.Rows.Count, rngTopOfList.Column).End(xlUp).Row
MasterSheet = ActiveWorkbook.Sheets.Count

If LR > rngTopOfList.Row Then

For i = 0 To LR - rngTopOfList.Row
ActiveWorkbook.Sheets(MasterSheet).Copy _
after:=Worksheets(MasterSheet + i - 1)
ActiveWorkbook.Sheets(MasterSheet + i).Name = _
rngTopOfList.Offset(i).Value

wsName = rngTopOfList.Offset(i).Value
x = 0

For j = 1 To 42

ActiveWorkbook.Sheets("Class").Range("C9").Offset(x, n).Value = _
"=" & wsName & "!M" & x + 9

x = x + 1
Next j

n = n + 1

Next i
'''
End If

ActiveWorkbook.Sheets(MasterSheet).Name = rngTopOfList.Value
Worksheets("MasterSheet").Visible = xlSheetVeryHidden
'Worksheets("Tools").Visible = xlSheetVeryHidden
End With

Set rngTopOfList = Nothing

As you can see the macro looks at the list found on the sheet called Tools and then creates sheets using a Template which I called MasterSheet.
What I need to do is eventually add additional sheets if it is necessary.

Brutium

 

[JoeMo]

One way to approach this would be to count the sheets in the existing workbook (curShtCt = ThisWorkbook.Sheets.Count) and compare it to the list count. If the list count is greater, then you have added new items to the list. You can then copy the master and add those sheets using a For Next that begins at ListCt-curShtsCt and goes through ListCt.

 

[Brutium]

Hello JoeMo,
Thank you for your prompt response, I really appreciate it.
I don't think I would be able to add your suggestion to my code. I can't figure out where I should add the suggested piece of code. Could you show me how you would do it?

Thanks.

Brutium

 

[JoeMo]

I need to know the structure of your workbook to add the code; how many sheets are in it before you begin adding sheets from your list, and so on. If you PM me, I will send you an email address where you can send a copy of your workbook.

 

[Brutium]

Hello JoeMo,
I sent you a copy of my workbook with an explanation of how it works, but I have not gotten any reply from you. Did you get my email to your PM? If not, please let me know and I will resend it.

Thank you for your help

Brutium

 

[JoeMo]

Hello Brutium,
I received your workbook and replied to your email with some questions. But, no matter, I have a revised workbook for you to try. Can you please send me another email and I will attach the workbook to a reply. Also, let me know if you didn't get my first reply. In that case, let me know if there is an alternative email address I can use.
Joe

 

[Brutium]

Hello JoeMo,

My mistake, I did receive your email. It ended up being hidden between other emails and I did not noticed that it was yours.

THANK YOU for all your work. It works much better than what I was expecting.

You are the best!!!

Thanks again

Brutium