Inputing data into Pivot Table

[YMTEO]

Currently I am in the process of doing up a Qtr 3 forecast template for the
sales man.

I use pivot table to churn out the results of the past months and the to go
months into a nice table.

Before I pass the pivot table to the salesman for their input, I need to
convert the pivot table into a static table and change all the sub total with
@sum formulars.

As the "static" pivot table has many sub total, inserting the sub total
formulars for each product groups are very tedious, not to say I have about
200+ sub products and 10 different tables.

Wondering is there a faster way to carry out this task?
It would be great if
1) I can add or change information within a Pivot table and have it stay
within the table and left the data source unchange?
2) A quick way to replace the pivot table sub total into @sum formular so
that when the figures within the "static" pivot table is changed, the sub
total will change accordingly too?

Can someone advise and help?

 

[Tim879]

As far as replacing the formula... the easiest method would be to do
the following...

1) remove all subtotals from the current pivot
2) make a copy of the pivot and only paste values
3) fill in the blank rows of the pivot with the data from above (see
macro below or do it manually)
4) use the built in excel subtotal function (data->subtotal) to insert
the formulas.


Sub Fill_Empty()
Dim selected_range As Range
continue = MsgBox("This macro will fill empty cells with the value
from the cell above. Do you want to continue?", vbYesNo)

If continue = vbNo Then
Exit Sub
ElseIf continue = vbYes Then
Set selected_range = Selection
On Error GoTo Error_Found:

Dim oRng As Range
Set oRng = Selection

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
oRng.Copy
oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

selected_range.Select
Application.CutCopyMode = False ' exit copy / paste mode

Exit Sub

End If


Error_Found:
MsgBox "Error: Either there are no blank cells in the range you
selected to fill " & _
"or there is no value in the cell above to fill down.
Please highlight a " & _
"range with blank cells and ensure there is data in the
first row of your " & _
"selection and try again.", vbInformation

End Sub

 

[YMTEO]

Tim,

Just want to confirm the steps again....

1) remove all subtotals from the current pivot
2) make a copy of the pivot and only paste values
3) fill in the blank rows of the pivot with the data from above (see
macro below or do it manually)
4) use the built in excel subtotal function (data->subtotal) to insert
the formulas.

If all the sub grouping totals are remove from the pivot table, after
copying and pasted value into another worksheet, I will need to insert blank
rows back into the worksheet again for each sub group. Am I right?

Wouldn't that take up even more time, as I have many sub-grouping in the
pivot table?

As far as replacing the formula... the easiest method would be to do
the following...

1) remove all subtotals from the current pivot
2) make a copy of the pivot and only paste values
3) fill in the blank rows of the pivot with the data from above (see
macro below or do it manually)
4) use the built in excel subtotal function (data->subtotal) to insert
the formulas.


Sub Fill_Empty()
Dim selected_range As Range
continue = MsgBox("This macro will fill empty cells with the value
from the cell above. Do you want to continue?", vbYesNo)

If continue = vbNo Then
Exit Sub
ElseIf continue = vbYes Then
Set selected_range = Selection
On Error GoTo Error_Found:

Dim oRng As Range
Set oRng = Selection

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
oRng.Copy
oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

selected_range.Select
Application.CutCopyMode = False ' exit copy / paste mode

Exit Sub

End If


Error_Found:
MsgBox "Error: Either there are no blank cells in the range you
selected to fill " & _
"or there is no value in the cell above to fill down.
Please highlight a " & _
"range with blank cells and ensure there is data in the
first row of your " & _
"selection and try again.", vbInformation

End Sub

Currently I am in the process of doing up a Qtr 3 forecast template for the
sales man.

I use pivot table to churn out the results of the past months and the to go
months into a nice table.

Before I pass the pivot table to the salesman for their input, I need to
convert the pivot table into a static table and change all the sub total with
@sum formulars.

As the "static" pivot table has many sub total, inserting the sub total
formulars for each product groups are very tedious, not to say I have about
200+ sub products and 10 different tables.

Wondering is there a faster way to carry out this task?
It would be great if
1) I can add or change information within a Pivot table and have it stay
within the table and left the data source unchange?
2) A quick way to replace the pivot table sub total into @sum formular so
that when the figures within the "static" pivot table is changed, the sub
total will change accordingly too?

Can someone advise and help?