ExcelBanter - sheet builder macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - sheet builder macro (https://www.excelbanter.com/excel-programming/340967-sheet-builder-macro.html)

sheet builder macro

I once was given a macro in this forum which, once clicked, gave me a
verticle column of sheet names in the active workbook in the active sheet. I
find it very useful, but I'd like to see if someone can help me with what
amounts to the converse of that.

I'd like to have a sheet with a verticle listing of names in column A which
designates the sheet names I'd like the active workbook to have. In other
words, I'd like to assign a macro to a button which will create the
additional tabs based on the list in the active sheet in column A.

Any help will be greatly appreciated.

tia,
Dave

sheet builder macro

Try this Macro...
Attach it to the button
Cell(A1) contains the Title Cells(A2,A3...) contain Sheet Names to be added

Sub AndrewMacro1()
Dim L As Integer
thisSht = ActiveSheet.Name
For L = 1 To 10 ' Max number of sheets to add
'this can be set as high as you like
Sheets(thisSht).Select
Cells(L + 1, 1).Select ' Select Next Cell down
If Selection.Value = "" Then Exit For
ShtName = Selection.Value
Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ShtName
Next L
MsgBox "Added " + Str(L - 1) + " Worksheets"
End Sub

HTH
Andrew

"Dave Breitenbach" wrote in
message ...
|I once was given a macro in this forum which, once clicked, gave me a
| verticle column of sheet names in the active workbook in the active sheet.
I
| find it very useful, but I'd like to see if someone can help me with what
| amounts to the converse of that.
|
| I'd like to have a sheet with a verticle listing of names in column A
which
| designates the sheet names I'd like the active workbook to have. In other
| words, I'd like to assign a macro to a button which will create the
| additional tabs based on the list in the active sheet in column A.
|
| Any help will be greatly appreciated.
|
| tia,
| Dave

sheet builder macro

Public Sub AddSheets()
Dim rngNames As Range
Dim rngCurrent As Range
Dim wks As Worksheet
Dim wksNew As Worksheet

Set wks = ActiveSheet
Set rngNames = wks.Range(wks.Range("A2"), wks.Cells(Rows.Count,
"A").End(xlUp))
For Each rngCurrent In rngNames
If Not (SheetExists(rngCurrent.Value)) Then
Set wksNew = Worksheets.Add
wksNew.Name = rngCurrent.Value
End If
Next rngCurrent
Exit Sub

End Sub

Public Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

--
HTH...

Jim Thomlinson

"Dave Breitenbach" wrote:

I once was given a macro in this forum which, once clicked, gave me a
verticle column of sheet names in the active workbook in the active sheet. I
find it very useful, but I'd like to see if someone can help me with what
amounts to the converse of that.

I'd like to have a sheet with a verticle listing of names in column A which
designates the sheet names I'd like the active workbook to have. In other
words, I'd like to assign a macro to a button which will create the
additional tabs based on the list in the active sheet in column A.

Any help will be greatly appreciated.

tia,
Dave

sheet builder macro

This is terrific. Thanks! One question. What is the necessity of A1 as a
title? I dont see where it is used. I shifted the names down to starting in
a2 as that gets the result I wanted.

thanks again,
Dave

"Andrew" wrote:

Try this Macro...
Attach it to the button
Cell(A1) contains the Title Cells(A2,A3...) contain Sheet Names to be added

Sub AndrewMacro1()
Dim L As Integer
thisSht = ActiveSheet.Name
For L = 1 To 10 ' Max number of sheets to add
'this can be set as high as you like
Sheets(thisSht).Select
Cells(L + 1, 1).Select ' Select Next Cell down
If Selection.Value = "" Then Exit For
ShtName = Selection.Value
Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = ShtName
Next L
MsgBox "Added " + Str(L - 1) + " Worksheets"
End Sub

HTH
Andrew

"Dave Breitenbach" wrote in
message ...
|I once was given a macro in this forum which, once clicked, gave me a
| verticle column of sheet names in the active workbook in the active sheet.
I
| find it very useful, but I'd like to see if someone can help me with what
| amounts to the converse of that.
|
| I'd like to have a sheet with a verticle listing of names in column A
which
| designates the sheet names I'd like the active workbook to have. In other
| words, I'd like to assign a macro to a button which will create the
| additional tabs based on the list in the active sheet in column A.
|
| Any help will be greatly appreciated.
|
| tia,
| Dave

sheet builder macro