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

Add new sheets with code
 

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

Add new sheets with code
 

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

Add new sheets with code
 

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

Add new sheets with code
 

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

Add new sheets with code
 

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