Thread: Add new sheets with code

I want to add additional sheets to my workbook with VBA. My workbook is
titled Habitat and I want to add the sheets titled "AllVolunteers",
"Roofing", "Siding". I have many more to add but if you can help me with
these, that would be great. I looked at my recorded macro code, but it isn't
working when I try to run it. Thank you

Something like:

newsheets = Array("AllVolunteers", "Roofing", "Sidings")
For i = 1 To 3
Sheets.Add
ActiveSheet.Name = newsheets(i - 1)
Next i

HTH

"Linda" wrote:

> I want to add additional sheets to my workbook with VBA. My workbook is
> titled Habitat and I want to add the sheets titled "AllVolunteers",
> "Roofing", "Siding". I have many more to add but if you can help me with
> these, that would be great. I looked at my recorded macro code, but it isn't
> working when I try to run it. Thank you

Hi Linda,

The following code let's you add a sheet to your workbook and also give it a
name via an input box (there's also some checking to ensure that you don't
enter the name of an existing sheet or that you don't try to give a sheet no
name at all):

Sub AddSheets()
'Add a sheet with a particular name
Dim ShtName As String

On Error GoTo ErrMsg

ShtName = InputBox("Please enter the sheet name", "Sheet Name")
If ShtName <> "" Then
Sheets.Add
ActiveSheet.Name = ShtName
Else
MsgBox "Please enter a sheet name", vbOKOnly, "Missing Sheet Name"
End If

Exit Sub

ErrMsg:
MsgBox "Invalid sheet name, please modify sheet name manually",
vbOKOnly, "Invalid Sheet Name"
End Sub


"Linda" wrote:

> I want to add additional sheets to my workbook with VBA. My workbook is
> titled Habitat and I want to add the sheets titled "AllVolunteers",
> "Roofing", "Siding". I have many more to add but if you can help me with
> these, that would be great. I looked at my recorded macro code, but it isn't
> working when I try to run it. Thank you

Hi Toppers,

When I try to run this, I get an error, maybe I did this wrong, here is what
I did:Any suggestions

For i = 1 To 6
Sheets.Add
ActiveSheet.Name = Volunteers(i - 1)
ActiveSheet.Name = Plumbing(i - 2)
ActiveSheet.Name = Roofing(i - 3)

"Toppers" wrote:

> Something like:
>
> newsheets = Array("AllVolunteers", "Roofing", "Sidings")
> For i = 1 To 3
> Sheets.Add
> ActiveSheet.Name = newsheets(i - 1)
> Next i
>
> HTH
>
> "Linda" wrote:
>
> > I want to add additional sheets to my workbook with VBA. My workbook is
> > titled Habitat and I want to add the sheets titled "AllVolunteers",
> > "Roofing", "Siding". I have many more to add but if you can help me with
> > these, that would be great. I looked at my recorded macro code, but it isn't
> > working when I try to run it. Thank you

OOps I'm all set, please disregard my last e-mail. One other question
though, how do I add these after my Sheet one, these are placed before my
first sheet.

"Toppers" wrote:

> Something like:
>
> newsheets = Array("AllVolunteers", "Roofing", "Sidings")
> For i = 1 To 3
> Sheets.Add
> ActiveSheet.Name = newsheets(i - 1)
> Next i
>
> HTH
>
> "Linda" wrote:
>
> > I want to add additional sheets to my workbook with VBA. My workbook is
> > titled Habitat and I want to add the sheets titled "AllVolunteers",
> > "Roofing", "Siding". I have many more to add but if you can help me with
> > these, that would be great. I looked at my recorded macro code, but it isn't
> > working when I try to run it. Thank you

Option Explicit
Sub testme()
Dim NewSheets As Variant
Dim i As Long

NewSheets = Array("AllVolunteers", "Roofing", "Sidings")
For i = LBound(NewSheets) To UBound(NewSheets)
Sheets.Add after:=Sheets(1)
ActiveSheet.Name = NewSheets(i)
Next i
End Sub

You may want to change this:
For i = LBound(NewSheets) To UBound(NewSheets)
to
For i = uBound(NewSheets) To lBound(NewSheets) step -1

if the order of the new worksheets is important.

Linda wrote:
>
> OOps I'm all set, please disregard my last e-mail. One other question
> though, how do I add these after my Sheet one, these are placed before my
> first sheet.
>
> "Toppers" wrote:
>
> > Something like:
> >
> > newsheets = Array("AllVolunteers", "Roofing", "Sidings")
> > For i = 1 To 3
> > Sheets.Add
> > ActiveSheet.Name = newsheets(i - 1)
> > Next i
> >
> > HTH
> >
> > "Linda" wrote:
> >
> > > I want to add additional sheets to my workbook with VBA. My workbook is
> > > titled Habitat and I want to add the sheets titled "AllVolunteers",
> > > "Roofing", "Siding". I have many more to add but if you can help me with
> > > these, that would be great. I looked at my recorded macro code, but it isn't
> > > working when I try to run it. Thank you

--

Dave Peterson