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
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