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