changing pivot table into a formula table

Y

YMTEO

Hello,

I am working on some tables for forecasting.

Currently, to send an input table out to the sales man, I have to convert a
pivot table (with data) to regular table with formulas.
I have to copy the pivot and paste special values and manually add back my
formulas, ex =SUM. So that the sales man can overwrite the existing amount in
the table and not effecting the original source data.

Is there a more efficent way of doing this?
Currently the pivot tables has many sub total, and you can imagine how many
times I need to insert the formular into the regular table.
 
M

Mike H.

I am not sure I am totally understanding what your problem is. You send out
data to salesmen and they return the data and then you take that data and
convert it to a pivot table and then you convert the pivot table to a
summarized table and then you send that back out to the salesmen? I think if
I understood exactly what all was going on, I could help you. But can you
more clearly explain what you are doing. Maybe explain why you're doing it
too because perhaps I (or someone else) could then suggest a different way to
accomplish the same thing.
 
Y

YMTEO

Hi Mike,

I already have all the data from the past forecast.
I convert the data into pivot table, which is calendarised and it is grouped
by customer, salesman, Product Group and product items.

I copied and paste the pivot table into static table and send it out to the
sales man for their input and reforecast.

But by doing so, the subtotal is out. Thus I need to manually insert the @
sum for each sub grouping all over again.
This is pretty tedious, as I have many customers, 10 sales man, 30 product
group and many product items.


e.g
Customer Salesman Prodt Data Jan 08 Feb 08 Mar 08
ABABAB HC Prodt A Sales 2,569 2,569 2,569
Vol 107 107 107
Prodt B Sales 1,095 1,247 1,095
Vol 46 52 46
Prodt D Sales 7,440 8,355 7,592
Vol 316 348 316
Prodt F Sales 47 47 47
Vol 2 2 2
HC Sum of Sales 11,302 12,218 11,302
HC Sum of svolume 471 509 471

NK Prodt B Sales 2,590 2,590 2,590
Vol 127 127 127
Prodt C Sales 32,634 18,081 5,591
Vol 2,220 1,230 360
Prodt D Sales 12,288 16,580 15,358
Vol 800 390 360
Prodt E Sales 1,458 8,954 12,288
Vol 90 580 800
Prodt G Sales 32,634 6,615 5,292
Vol 2,220 450 360
NK Sum of Sales 81,604 52,820 41,118
NK Sum of svolume 5,457 2,777 2,007

ABABAB Sum of Sales 92,906 65,037 52,421
ABABAB Sum of svolume 5,928 3,286 2,478

CSCSCS HO Prodt A Sales 9,641 1,128 1,128
Vol 510 60 60
HO Sum of Sales 9,641 1,128 1,128
HO Sum of svolume 510 60 60

JK Prodt B Sales 40,986 40,986 8,197
Vol 2,700 2,700 540
Prodt C Sales 43,056 43,056 43,056
Vol 900 900 900
Prodt D Sales 8,410 2,803 18,221
Vol 720 240 1,560
Prodt E Sales 4,099 4,099 4,099
Vol 270 270 270
JK Sum of Sales 96,550 90,944 73,573
JK Sum of svolume 4,590 4,110 3,270

CSCSCS Sum of Sales 106,191 92,072 74,701
CSCSCS Sum of svolume 5,100 4,170 3,330
 
M

Mike H.

If your data is laid out like I think it is, then this should work. Try it
and if it doesn't, let me know.

Sub InsertTotals()
Dim DataArray(500, 5) As Variant
Dim DataArray2(500, 2) As Variant
Dim Cumulator2 As Long
Dim x As Long
Dim Y As Long
Dim LastRow As Long
Dim Cumulators As Long
Dim FString As String
Dim Found As Integer
Dim TheType As String

Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is
empty!
Let LastRow = ActiveCell.Row
x = 1
Let Cumulator2 = 0
Do While True
If x > LastRow Then
Exit Do
End If
If x = 11 Then
'Beep
End If
If Cells(x, 1).Value <> Empty Then
Let Cumulators = 0
End If
If Cells(x, 1).Value = "Customer" Then
ElseIf Cells(x, 2).Value Like "*Sum of Sale*" Then
If Cells(x, 2).Value Like "*Sale*" Then
Let TheType = "Sales"
Else
Let TheType = "Vol"
End If
Let FString = "="
Range("E" & x).Select
Let Found = 0
For Y = 1 To Cumulators
If DataArray(Y, 2) = TheType Then
If Found = 1 Then
Let FString = FString & "+R[" & -(x - DataArray(Y, 1)) &
"]C"
Else
Let FString = FString & "R[" & -(x - DataArray(Y, 1)) &
"]C"
Found = 1
End If
End If
Next
ActiveCell.FormulaR1C1 = FString
Range("E" & x).Select
Selection.Copy
Range("E" & x + 1).Select
ActiveSheet.Paste
Range("E" & x).Select

Selection.Copy
Range("F" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("F" & x + 1).Select
ActiveSheet.Paste

Selection.Copy
Range("G" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("G" & x + 1).Select
ActiveSheet.Paste
Let Cumulators = 0
Let Cumulator2 = Cumulator2 + 1
DataArray2(Cumulator2, 1) = x

'ElseIf Cells(x, 2).Value Like "*Sum of svolum*" Then



ElseIf Cells(x, 4).Value = "Sales" Then
Let Cumulators = Cumulators + 1
DataArray(Cumulators, 1) = x
DataArray(Cumulators, 2) = "Sales"
ElseIf Cells(x, 4).Value = "Vol" Then
Let Cumulators = Cumulators + 1
DataArray(Cumulators, 1) = x
DataArray(Cumulators, 2) = "Vol"
ElseIf Cells(x, 1).Value Like "*Sum of Sale*" Then
Let FString = "="
For Y = 1 To Cumulator2
Let FString = FString & "+R[" & -(x - DataArray2(Y, 1)) & "]C"
Next
Cells(x, 5).Select
ActiveCell.FormulaR1C1 = FString
Selection.Copy
Range("E" & x + 1).Select
ActiveSheet.Paste
Range("E" & x).Select

Selection.Copy
Range("F" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("F" & x + 1).Select
ActiveSheet.Paste

Selection.Copy
Range("G" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("G" & x + 1).Select
ActiveSheet.Paste
cumulators2 = 0
End If
x = x + 1
Loop


End Sub
 
Y

YMTEO

Hi Mike,

I tried to run the macro, but it say - "Can't execute in breakmode."
Not sure what it meant.

By the way, my data has more columns and rows than the example I gave.
I believe that should not affect the programming.
Am I right?
 
M

Mike H.

Can't operate in break mode means you have another macro that is "paused" and
you can't start another one with it paused. To clear it, select Run>Reset on
the menu in the editor. And yes, the number of columns would GREATLY affect
this macro as I based everything on what I saw in your example. If you can't
figure it out you'll have to give me the columns for all the items in your
data.


YMTEO said:
Hi Mike,

I tried to run the macro, but it say - "Can't execute in breakmode."
Not sure what it meant.

By the way, my data has more columns and rows than the example I gave.
I believe that should not affect the programming.
Am I right?


Mike H. said:
If your data is laid out like I think it is, then this should work. Try it
and if it doesn't, let me know.

Sub InsertTotals()
Dim DataArray(500, 5) As Variant
Dim DataArray2(500, 2) As Variant
Dim Cumulator2 As Long
Dim x As Long
Dim Y As Long
Dim LastRow As Long
Dim Cumulators As Long
Dim FString As String
Dim Found As Integer
Dim TheType As String

Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is
empty!
Let LastRow = ActiveCell.Row
x = 1
Let Cumulator2 = 0
Do While True
If x > LastRow Then
Exit Do
End If
If x = 11 Then
'Beep
End If
If Cells(x, 1).Value <> Empty Then
Let Cumulators = 0
End If
If Cells(x, 1).Value = "Customer" Then
ElseIf Cells(x, 2).Value Like "*Sum of Sale*" Then
If Cells(x, 2).Value Like "*Sale*" Then
Let TheType = "Sales"
Else
Let TheType = "Vol"
End If
Let FString = "="
Range("E" & x).Select
Let Found = 0
For Y = 1 To Cumulators
If DataArray(Y, 2) = TheType Then
If Found = 1 Then
Let FString = FString & "+R[" & -(x - DataArray(Y, 1)) &
"]C"
Else
Let FString = FString & "R[" & -(x - DataArray(Y, 1)) &
"]C"
Found = 1
End If
End If
Next
ActiveCell.FormulaR1C1 = FString
Range("E" & x).Select
Selection.Copy
Range("E" & x + 1).Select
ActiveSheet.Paste
Range("E" & x).Select

Selection.Copy
Range("F" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("F" & x + 1).Select
ActiveSheet.Paste

Selection.Copy
Range("G" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("G" & x + 1).Select
ActiveSheet.Paste
Let Cumulators = 0
Let Cumulator2 = Cumulator2 + 1
DataArray2(Cumulator2, 1) = x

'ElseIf Cells(x, 2).Value Like "*Sum of svolum*" Then



ElseIf Cells(x, 4).Value = "Sales" Then
Let Cumulators = Cumulators + 1
DataArray(Cumulators, 1) = x
DataArray(Cumulators, 2) = "Sales"
ElseIf Cells(x, 4).Value = "Vol" Then
Let Cumulators = Cumulators + 1
DataArray(Cumulators, 1) = x
DataArray(Cumulators, 2) = "Vol"
ElseIf Cells(x, 1).Value Like "*Sum of Sale*" Then
Let FString = "="
For Y = 1 To Cumulator2
Let FString = FString & "+R[" & -(x - DataArray2(Y, 1)) & "]C"
Next
Cells(x, 5).Select
ActiveCell.FormulaR1C1 = FString
Selection.Copy
Range("E" & x + 1).Select
ActiveSheet.Paste
Range("E" & x).Select

Selection.Copy
Range("F" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("F" & x + 1).Select
ActiveSheet.Paste

Selection.Copy
Range("G" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("G" & x + 1).Select
ActiveSheet.Paste
cumulators2 = 0
End If
x = x + 1
Loop


End Sub
Click to expand...
Click to expand...
 
Y

YMTEO

The real format is exactly the same as the e.g I gave, except that the real
columns are extended to Dec 2008.

What about the number of rows, will that affect the macro too?
I could not provide you the exact number of rows as each customer has
different number of product and may increase.

Mike H. said:
Can't operate in break mode means you have another macro that is "paused" and
you can't start another one with it paused. To clear it, select Run>Reset on
the menu in the editor. And yes, the number of columns would GREATLY affect
this macro as I based everything on what I saw in your example. If you can't
figure it out you'll have to give me the columns for all the items in your
data.


YMTEO said:
Hi Mike,

I tried to run the macro, but it say - "Can't execute in breakmode."
Not sure what it meant.

By the way, my data has more columns and rows than the example I gave.
I believe that should not affect the programming.
Am I right?


Mike H. said:
If your data is laid out like I think it is, then this should work. Try it
and if it doesn't, let me know.

Sub InsertTotals()
Dim DataArray(500, 5) As Variant
Dim DataArray2(500, 2) As Variant
Dim Cumulator2 As Long
Dim x As Long
Dim Y As Long
Dim LastRow As Long
Dim Cumulators As Long
Dim FString As String
Dim Found As Integer
Dim TheType As String

Range("A65000").End(xlUp).Select 'this is a row with data, this row +1 is
empty!
Let LastRow = ActiveCell.Row
x = 1
Let Cumulator2 = 0
Do While True
If x > LastRow Then
Exit Do
End If
If x = 11 Then
'Beep
End If
If Cells(x, 1).Value <> Empty Then
Let Cumulators = 0
End If
If Cells(x, 1).Value = "Customer" Then
ElseIf Cells(x, 2).Value Like "*Sum of Sale*" Then
If Cells(x, 2).Value Like "*Sale*" Then
Let TheType = "Sales"
Else
Let TheType = "Vol"
End If
Let FString = "="
Range("E" & x).Select
Let Found = 0
For Y = 1 To Cumulators
If DataArray(Y, 2) = TheType Then
If Found = 1 Then
Let FString = FString & "+R[" & -(x - DataArray(Y, 1)) &
"]C"
Else
Let FString = FString & "R[" & -(x - DataArray(Y, 1)) &
"]C"
Found = 1
End If
End If
Next
ActiveCell.FormulaR1C1 = FString
Range("E" & x).Select
Selection.Copy
Range("E" & x + 1).Select
ActiveSheet.Paste
Range("E" & x).Select

Selection.Copy
Range("F" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("F" & x + 1).Select
ActiveSheet.Paste

Selection.Copy
Range("G" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("G" & x + 1).Select
ActiveSheet.Paste
Let Cumulators = 0
Let Cumulator2 = Cumulator2 + 1
DataArray2(Cumulator2, 1) = x

'ElseIf Cells(x, 2).Value Like "*Sum of svolum*" Then



ElseIf Cells(x, 4).Value = "Sales" Then
Let Cumulators = Cumulators + 1
DataArray(Cumulators, 1) = x
DataArray(Cumulators, 2) = "Sales"
ElseIf Cells(x, 4).Value = "Vol" Then
Let Cumulators = Cumulators + 1
DataArray(Cumulators, 1) = x
DataArray(Cumulators, 2) = "Vol"
ElseIf Cells(x, 1).Value Like "*Sum of Sale*" Then
Let FString = "="
For Y = 1 To Cumulator2
Let FString = FString & "+R[" & -(x - DataArray2(Y, 1)) & "]C"
Next
Cells(x, 5).Select
ActiveCell.FormulaR1C1 = FString
Selection.Copy
Range("E" & x + 1).Select
ActiveSheet.Paste
Range("E" & x).Select

Selection.Copy
Range("F" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("F" & x + 1).Select
ActiveSheet.Paste

Selection.Copy
Range("G" & x).Select
ActiveSheet.Paste
Selection.Copy
Range("G" & x + 1).Select
ActiveSheet.Paste
cumulators2 = 0
End If
x = x + 1
Loop


End Sub
Click to expand...
Click to expand...
Click to expand...
 
M

Mike H.

The number of rows is not relevant. The macro should take that just fine.
To expand the columns you'd need to modify the code
'you'd need to add code to continue out to H, I etc til you get to the Dec
column...

 
F

Flu

thanks.
That is of great help.

Mike H. said:
The number of rows is not relevant. The macro should take that just fine.
To expand the columns you'd need to modify the code

'you'd need to add code to continue out to H, I etc til you get to the Dec
column...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Alternative to pivot table ? 8
Automatically Update a Pivot Table Field Used as Column / Row 1
Creating a Calculated Field in a Pivot Table for same field 3
Pivot Table with Percentages 4
Pivot table code question 2
Playing back a Pivot Table macros 2
Pivot table "Sum of" or "Count of" 1
Add result feilds to a pivot table 3

Top