Maintain Formulas - Split data into Multiple Worksheets

[amysmith]

I am currently trying to split data into multiple worksheets based on column A and have used the below macro (which I found on this site). My master worksheet has formulas, but these are deleted and only values are copied to the split worksheets.

Is there a way to adjust the macro so that the formulas are copied in the new worksheets instead of values only?

Thank you!


Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Set wSheetStart = ActiveSheet
wSheetStart.AutoFilterMode = False
'Set a range variable to the correct item column
Set rRange = Range("A1", Range("A65536").End(xlUp))

'Delete any sheet called "UniqueList"
'Turn off run time errors & delete alert
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("UniqueList").Delete

'Add a sheet called "UniqueList"
Worksheets.Add().Name = "UniqueList"

'Filter the Set range so only a unique list is created
With Worksheets("UniqueList")
rRange.AdvancedFilter xlFilterCopy, , _
Worksheets("UniqueList").Range("A1"), True

'Set a range variable to the unique list, less the heading.
Set rRange = .Range("A2", .Range("A65536").End(xlUp))
End With

On Error Resume Next
With wSheetStart
For Each rCell In rRange
strText = rCell
.Range("A1").AutoFilter 1, strText
Worksheets(strText).Delete
'Add a sheet named as content of rCell
Worksheets.Add().Name = strText
'Copy the visible filtered range _
(default of Copy Method) and leave hidden rows
.UsedRange.Copy Destination:=ActiveSheet.Range("A1")
ActiveSheet.Cells.Columns.AutoFit
Next rCell
End With

With wSheetStart
.AutoFilterMode = False
.Activate
End With

On Error GoTo 0
Application.DisplayAlerts = True
End Sub

 

[sheetspread]

http://www.mrexcel.com/forum/showthread.php?t=596188

 

[amysmith]

With that macro, the formulas are maintained in the first worksheet created. The additional worksheets created there are no formulas, just values of the resultant.

 

[sheetspread]

I just tested it and the formulas appeared on each sheet:

Excel 2003
	A	B	C	D
1	Group	Type	Number	Formula
2	Orange	Alpha	5	26
3	Orange	Beta	3	10
4	Orange	Gamma	6	37
5	Orange	Delta	0	1
6	Orange	Epsilon	1	2
7	Orange	Theta	1	2
8	Orange	Phi	5	26
9	Orange	Psi	4	17
10	Blue	Omicron	3	10
11	Blue	Theta	1	2
12	Blue	Sigma	9	82
13	Blue	Kappa	1	2
14	Blue	Iota	6	37
15	Green	Pi	9	82
16	Green	Tau	2	5
17	Violet	Zeta	6	37
18	Violet	Omega	8	65
19	Violet	Mu	2	5
20	Violet	Nu	9	82
Data

Cell Formulas
Range	Formula
D2	=C2^2+1

Be sure the sheet is named "Data", the column containing the split points "Group", and delete all other tabs.

 

[amysmith]

I retried; you are correct!

THANK YOU SOOOO MUCH!