Need help creating a NEW workbook with multiple sheets

[chanakyas]

hi guys,
I'm pretty new to Macros. so need some help here

My boss had a macro that he had been using to create multiple sheets in an existing Workbook. So this macro extracts certain data from the current workbook and creates multiple tabs with data filled into it.

problem is the resulting file is a massive sized one and it slows down the system greatly.

what i would like to do is, create a new workbook and all the new tabs in this new workbook instead of the existing one so we have two workbooks, one just with the base data and the other with all the tabs with the resulting data.

And it would be great, If I can put a button on the main workbook which would run this macro to create the new workbook and the tabs in it.

The macro that i have on hand right now to create the multiple tabs is as below:

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Control Sheet").Range("C7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

I found a macro online (as below) for creating a new workbook from an existing one. but when I tried combining them both, it didn't work.

Sub Create_New_Workbook()
Dim wbNew As Workbook, wbCur As Workbook




Set wbNew = ActiveWorkbook

With wbNew
SaveAs Filename:=Range("Spotless_30 Sites_Updated") & ".xlsx"
End With
End Sub

can anyone please help me combine these two or give me a new way of doing what i set out to do?

Thanks heaps guys!

cheers!
Chan

 

[Akuini]

add this above 'For Each MyCell In MyRange':
Workbooks.Add

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet1").Range("C7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

Workbooks.Add
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.count) 'creates a new worksheet
Sheets(Sheets.count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

 

[chanakyas]

Thanks Akuini!

When i run the code
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range


Set MyRange = Sheets("Sheet1").Range("C7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))


Workbooks.Add
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

it throws an error
Run-time error '9':
Subscript out of range

any idea why? Thanks!

 

[Akuini]

Sorry, I shouldn’t change ("Control Sheet") to ("Sheet1").
Use this one:

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Control Sheet").Range("C7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

Workbooks.Add
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.count) 'creates a new worksheet
Sheets(Sheets.count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

 

[chanakyas]

Akuini, you legend!! Awesome, That works..Thanks mate!

now, will I be able to use this code in a command button code?

For example, If i create a command button using

"Private Sub CommandButton1_Click()

End Sub"

will I be able to use the above Sheet/Tab generation code inside the command button so I can click the button to generate this new worksheet and its tabs?

 

[chanakyas]

Then, there is also this other code which is used to populate some data from the main sheet to these newly created tabs.

can you please look into this code and see if it would work on a new sheet or if it can only work from the main sheet where the base data is, mate? or is there a way I can point this second bit of code to the main data?

Thanks a ton!

Sub Formula_Zapper()
Worksheets.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Select
Application.CutCopyMode = False
End Sub

 

[Akuini]

You can do this:

1. Open the worksheet ‘Control Sheet’
2. In the menu click developer> insert > choose ‘Button’ and place it anywhere in the worksheet.
3. Right click the button > assign macro > choose the macro ‘CreateSheetsFromAList’> OK.

 

[Akuini]

1. The main sheet, is it in the same workbook with ‘Control Sheet’?
2. Do you want to copy data in main sheet to each of the new sheets just created before?
3. Do you want to copy all data or only some data from main sheet? If it is only some data then you have to specify where it is located.

 

[chanakyas]

Hey mate,
Thanks for looking into this.

1. The main sheet, is it in the same workbook with ‘Control Sheet’?
Yes, the data sheet is in the same workbook as control sheet

2. Do you want to copy data in main sheet to each of the new sheets just created before?
Yes, want to pull out certain data from the main data sheet to the new sheets we created above in the new workbook

3. Do you want to copy all data or only some data from main sheet? If it is only some data then you have to specify where it is located.
only some data, which are relevant to the individual tabs that we created in the new workbook

Thanks again bud!

 

[Akuini]

Well, as I said, if it is only some data then you have to specify where it is located. I mean what cells in the main sheet? Copy it to which new sheet?
Can you give some example?