changing pivot table into a formula table
 

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.

changing pivot table into a formula table
 

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.

"YMTEO" wrote:

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.

changing pivot table into a formula table
 

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

changing pivot table into a formula table
 

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." wrote:

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

changing pivot table into a formula table
 

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 RunReset 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" wrote:

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." wrote:

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

changing pivot table into a formula table
 

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." wrote:

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 RunReset 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" wrote:

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." wrote:

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

changing pivot table into a formula table
 

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

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


Let Cumulators = 0
Let Cumulator2 = Cumulator2 + 1
DataArray2(Cumulator2, 1) = x

changing pivot table into a formula table
 

thanks.
That is of great help.

"Mike H." wrote:

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

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


Let Cumulators = 0
Let Cumulator2 = Cumulator2 + 1
DataArray2(Cumulator2, 1) = x