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

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" <[email protected]> wrote in
message news:[email protected]...
|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

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

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" <[email protected]> wrote in
> message news:[email protected]...
> |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
>
>
>

Dont Need A1 to have title,
Change Cells(L + 1, 1).Select ' Select Next Cell down
to: Cells(L , 1).Select ' Select Next Cell down
if you dont have a title in..!

Happy Excelling..!
Andrew

"Dave Breitenbach" <[email protected]> wrote in
message news:[email protected]...
| 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" <[email protected]> wrote in
| > message news:[email protected]...
| > |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
| >
| >
| >