A new to create a Wksheet in a Macro with 10 Tabs

[Bob Ully]

I need to create a Worksheet within a macro that has 10 tabs. I had something running, then I made some changes and I got Application-defined or object defined error.

Sheets("Sheet1").Name=Sheetname(1)

Sheets.Add after:=Sheets(Sheets.Count)

I was looping thru this up to 10 now its not working. This isn't too complicated, any ideas. Thanks.

Bob U.

 

[hiker95]

Bob Ully,

What version of Excel and Windows are you using?

1. Can we see your present macro code?

If posting VBA code, please use Code Tags - like this:

[code]

Paste your code here.

[/code]


2. And, what will be the names of the 10 new worksheets?

 

[Bob Ully]

Bob Ully,

What version of Excel and Windows are you using?
Excel 2007, Windows XP

1. Can we see your present macro code?

Here's my code

Public Sheetname(20) As String
Public SourceName(20) As String
Public RateChg(20) As Single
Public CopyName(20) As String
Public CellName(20) As String

'Open New Workbook
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=F1
Windows(F1).Activate
Sheets("Sheet1").Select
'Add Sheets if needed

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet4").Name = Sheetname(4)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet5").Name = Sheetname(5)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet6").Name = Sheetname(6)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet7").Name = Sheetname(7)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet8").Name = Sheetname(8)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet9").Name = Sheetname(9)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet10").Name = Sheetname(10)


2. And, what will be the names of the 10 new worksheets?

sheet1, sheet2, sheet3, etc.

 

[hiker95]

Bob Ully,

Can we see all the present macro code not wrapped in a Quote?

 

[Bob Ully]

I had to clean out other code. As I said I get an "Application-defined or object defined error". Thanks. Bob U.




Public Sheetname(20) As String
Sub Macro1()
' Macro1 Macro
F1 = Range("C5").Value
'Open New Workbook
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=F1
Windows(F1).Activate
Sheets("Sheet1").Select
'Add Sheets if needed

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet4").Name = Sheetname(4)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet5").Name = Sheetname(5)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet6").Name = Sheetname(6)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet7").Name = Sheetname(7)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet8").Name = Sheetname(8)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet9").Name = Sheetname(9)

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet10").Name = Sheetname(10)

End Sub

 

[hiker95]

Bob Ully,

I have attempted to update your macro code without any success.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[Norie]

Where are you populating the SheetName array?

 

[Bob Ully]

Bump

 

[Bob Ully]

I found another way to do this.
I used :

Application.SheetsInNewWorkBook=10

Then I reset this back to 3 at the end of the macro. Thanks.

 

[hiker95]

Bob Ully,

Thanks for the feedback.

I am glad that you solved your own request.

You are very welcome.

And, come back anytime.