Create a workbook in vba and have the sheet names from a range 1

[Epsilon101]

Hi,
I am trying to do the below:

Have a macro that will create a new workbook, with new sheets, amounts and names of which are dependant on a list in column A.

So
A1 = Menu
A2 = User
A3 = Data

which will create a new workbook with 3 worksheets in it called 'menu' 'user' and 'data'.

I have tried to do this a few times i just dont have a lot of practise with excel vba syntax and i cant find any useful examples from excel help.






---------------------------------------

Neil

 

[TonyJollans]

Hi Epsilon101,

Try this for size ..

[blue]Sub NewBook()

Dim objSheet As Worksheet
Set objSheet = ActiveSheet
Dim objCell As Range
Dim iNewSheets As Integer, iRow As Integer

Set objSheet = ActiveSheet

iNewSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = ActiveSheet.Range("A65536").End(xlUp).Row
Workbooks.Add
Application.SheetsInNewWorkbook = iNewSheets

For iRow = 1 To ActiveWorkbook.Sheets.Count
    ActiveWorkbook.Sheets(iRow).Name = objSheet.Cells(iRow, 1)
Next

End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Epsilon101]

Ah cool thanks Tony,

the sheetsInNewWorkbook property is handy to know.

Here is what ive got after reading through it, please correct me if im wrong.

Sub NewBook()

Dim objSheet As Worksheet
[COLOR=green]'XXXX Dont need this set, is done below XXXX[/color green]
Set objSheet = ActiveSheet
[COLOR=green]'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/color green]
Dim objCell As Range
Dim iNewSheets As Integer, iRow As Integer

Set objSheet = ActiveSheet [COLOR=green]'objsheet variable = the current activesheet[/color green]

[COLOR=green]'iNewSheets integer variable = no. of worksheets to be added to the new workbook or no. alrdy existing.[/color green]
iNewSheets = Application.SheetsInNewWorkbook
[COLOR=green]'SheetsinNewWorkbook count = last row in sheet with the name list[/color green]
Application.SheetsInNewWorkbook = ActiveSheet.Range("A65536").End(xlUp).Row
[COLOR=green]'adds a new workbook[/color green]
Workbooks.Add

[COLOR=green]'XXXX NO loop here so dont need this bit XXXX[/color green]
[COLOR=green]'position of the last name in list = iNewSheets integer[/color green]
Application.SheetsInNewWorkbook = iNewSheets
[COLOR=green]'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/color green]

[COLOR=green]'loop from iRow = 1 to New workbooks sheet count which is SheetsInNewWorkbook[/color green]
For iRow = 1 To ActiveWorkbook.Sheets.Count
[COLOR=green]'each loop will sheet.name and change it to activesheet's cell position (iRow, column 1)[/color green]
[COLOR=green]'so IRow = 1 Sheet(1).Name = activesheet cell(1,1)[/color green]
    ActiveWorkbook.Sheets(iRow).Name = objSheet.Cells(iRow, 1)
Next
[COLOR=green]'ends once loop reaches limit[/color green]
End Sub

---------------------------------------

Neil

 

[TonyJollans]

Hi Neil,

Sorry about the duplicate Set statement - meant to move it but copied instead.

The iNewSheets variable is, perhaps, badly named as it is just used as a bucket to save the user's current value while the code hijacks the property setting. Your comment that it is the last name in the list is incorrect.

Otherwise it looks good.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Epsilon101]

Thanks for checking it Tony.

Did you mean this bit?

'position of the last name in list = iNewSheets integer
Application.SheetsInNewWorkbook = iNewSheets

Didnt Application.SheetsInNewWorkbook equal the last row that contains a cell of data in column A.

So last name is in cell A4 it goes to cell A4, row number is 4 so Application.SheetsInNewWorkbook = 4???

That wrong??



---------------------------------------

Neil

 

[TonyJollans]

Yes, Neil,

Application.SheetsInNewWorkbook doesequal the last row that contains a cell of data in column A - because you've just set it that way, but that statement is setting it back to the saved value - it's just housekeeping, so as not to permanently change the user's environment.

The code is just using the SheetsInNewWorkbook setting as a quick way to get the right number of sheets - when it's done that, it resets the parameter and works with what it's created (using Sheets.Count - the number of sheets actually in the workbook just created)

So, you have

[blue]:
:

[green][b]' Save the user's setting[/b][/green]
iNewSheets = Application.SheetsInNewWorkbook

[green][b]' Set Excel value to number of populated rows in column A[/b][/green]
Application.SheetsInNewWorkbook = ActiveSheet.Range("A65536").End(xlUp).Row

[green]'adds a new workbook[/green]
Workbooks.Add

[green][b]Reset Excel value to user's original setting[/b][/green]
Application.SheetsInNewWorkbook = iNewSheets

[green]'loop from iRow = 1 to New workbooks sheet count which is [i]NO LONGER[/i] SheetsInNewWorkbook[/green]
For iRow = 1 To ActiveWorkbook.Sheets.Count

:
:[/blue]

Is that clearer. Or are we saying the same thing anyway?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Epsilon101]

Ah thanks i get you now, its just me thinking of Application.SheetsInNewWorkbook[/color blue] acting the same as a variable, when i know it isnt.

Thanks for the explanation to clear that up

---------------------------------------

Neil