PDA

View Full Version : Create sheets from master but copy them to new book



Diddy
09-14-2011, 02:10 AM
Hi everyone,

I have this bit of code which I think someone here a long time ago kindly provided
Option Explicit
Sub CreateSheets()
Dim Rng As Range, MyCell As Range
Application.ScreenUpdating = False
Set Rng = Sheets("list").Range("PropagateSheets")
For Each MyCell In Rng
Sheets("Sheet1").Range("aq1").Value = MyCell.Value
With Sheets("Sheet1")
.Copy Before:=Sheets(2)
Cells.Select
Range("aq1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.FormulaR1C1 = "`"
Range("aq1").Select
ActiveSheet.Name = .Range("aq1").Value
End With
Sheets("Sheet1").Select
Next
Application.ScreenUpdating = True
End Sub


Sheet 1 is a master sheet and data contained in it is pulled from other sheets in the same workbook using index match formula based on cell AQ1. The code changes AQ1 from a list of numbers (about 60) in Sheet "List" and creates a sheet for each one copying values.
This has done just what I needed until I've added more data sheets and i get the message too many fonts.

Is it possible to create sheets that automatically save to a new workbook?

Grateful for any help : pray2:

JWest
09-14-2011, 07:28 AM
This seems to be a problem in excel when you are creating too many spreadsheets with a lot of formatting/fonts. So you may be able to avoid having to create another workbook by simplifying the formatting within your spreadsheets. I'm not sure of the exact number, but you can create +5000 spreadsheets within a workbook provided you don't overwhelm the workbook with formatting.

Alternatively, if you want the data to copy to another workbook you would insert:



.Copy Before:=Workbooks("Book2").Sheets(2)



Where "Book2" is the name of your new workbook. The problem with this approach is that you will eventually end up with the same error in the new workbook as it fills with data over time.

Diddy
09-15-2011, 02:23 AM
Hi JWest,

Thanks for replying.
I have had an awful lot of formatting in the workbook - used to keep my place as I have been analysing the data. Sheet 1 has a fair bit. Would I be right in thinking that chart formatting would be included? Not an option at the moment to change this so I'm going with the new wkbk option.
I'm having trouble as it is bugging out at the copy line. I've tried opening a new book (book 2) before running but probably I'm missing something.
Many thanks
Diddy

JWest
09-15-2011, 04:32 PM
Yep, you are right in thinking that chart formatting would be included. In fact, most people stumble across the problem through chart formatting alone.

Remove the ".xls" from after Book2. This was an error on my point. New line should read:



.Copy Before:=Workbooks("Book2").Sheets(2)

Diddy
09-16-2011, 01:43 AM
Hi JWest,

There wasn't .xls in the original bit of code. Sounds a bit churlish to say after you helping me:o:
Sorry
Diddy

JWest
09-16-2011, 03:08 AM
Man, I must be losing it, haha!

Anyway, I'm not sure what the issue is. I have re-created the code and can run it fine from my end with the line changed to use "Book2". Make sure when you are running the code that you have "Book1" (or whatever the original workbook is called) as the active workbook. The code says to copy before Sheets(2) on "Book2" so there needs to be at least 2 sheets in "Book2" for it to work. If there aren't, then change it to Sheets(1).

Diddy
09-16-2011, 03:30 AM
Hi,

I've not tested it but sounds as though that's it. Sheets in new workbook is set up to 1.

Sorry to be thick!!!
Diddy

Diddy
09-16-2011, 07:56 AM
Hi JWest,

Now that I have Book 2 open with sufficient sheets (!) the code runs and creates the correct number of sheets. It's also naming the tabs according to the range on the list tab of the original workbook. the only prob is that the copy range is only copying to the 1st tab. All the other tabs are blank.
I'm guessing that when it loops back to sheet 1 it's sheet 1 of Book 2. Would you be able to help with code to go back to the original book Named Performance_Summary_v18 etc?: pray2:

Many thanks
Diddy