Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
from pivot table to vb array copy to new table
Sheet 1 has the data
consisting of a code, account,distribution center, sub total, total (columns A-D, respectively) In the pivot table we have it using sum to find totals of similar distribution centers. The account number is repetitive in the data sheet so it does not appear in the PT I need to do the same with each row, which is: if column C is < 0 when rounded to 2 decimal places OR if column D is < 0 when rounded to 2 decimal places, then copy the row to a temporary array(is this possible) Once all rows are checked, copy temp array to a specific worksheet. I am told this is the place to find answers like this. Can this be done? I have tried doing this on my own and have been unsuccessful to this point. If someone pulls this off, would you mind helping me understand how you got to said macro? I want to learn how to do this. Thank you, - g |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
from pivot table to vb array copy to new table
On 01/08/2010 06:36, greegan wrote:
Sheet 1 has the data consisting of a code, account,distribution center, sub total, total (columns A-D, respectively) In the pivot table we have it using sum to find totals of similar distribution centers. The account number is repetitive in the data sheet so it does not appear in the PT I need to do the same with each row, which is: if column C is< 0 when rounded to 2 decimal places OR if column D is< 0 when rounded to 2 decimal places, then copy the row to a temporary array(is this possible) Once all rows are checked, copy temp array to a specific worksheet. I am told this is the place to find answers like this. Can this be done? I have tried doing this on my own and have been unsuccessful to this point. If someone pulls this off, would you mind helping me understand how you got to said macro? I want to learn how to do this. Thank you, - g The structure of your data and what you are trying to do are not clear which will limit the replies you get. More explanation and the code you have tried might help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
from pivot table to vb array copy to new table
any assistance is appreciated. I would like to understand this process more
than anything thank you - g PS, I have no clue where my previous post went so I have copied the ng's last reply before my file info On 01/08/2010 06:36, greegan wrote: Sheet 1 has the data consisting of a code, account,distribution center, sub total, total (columns A-D, respectively) In the pivot table we have it using sum to find totals of similar distribution centers. The account number is repetitive in the data sheet so it does not appear in the PT I need to do the same with each row, which is: if column C is< 0 when rounded to 2 decimal places OR if column D is< 0 when rounded to 2 decimal places, then copy the row to a temporary array(is this possible) Once all rows are checked, copy temp array to a specific worksheet. I am told this is the place to find answers like this. Can this be done? I have tried doing this on my own and have been unsuccessful to this point. If someone pulls this off, would you mind helping me understand how you got to said macro? I want to learn how to do this. Thank you, - g The structure of your data and what you are trying to do are not clear which will limit the replies you get. More explanation and the code you have tried might help. This is what I have now Data tab(copied from separate file) Code,Acct,Division,MonthEnd,Total 0040,A10001,10001/8888-1111,26270.75,184798.82 0040,A10001,10001/8888-1112,0.00,-158528.07 0048,A10001,10001/8888-1113,107859.88,754890.66 0048,A10001,10001/8888-1114,0.00,-647030.78 0051,A10001,10001/8888-1115,17814.65,126728.16 0051,A10001,10001/8888-1116,0.00,-108913.51 PivotTable1 on Totals tab Row Labels,Division,Sum of MonthEnd,Sum of Total 0040,10001/8888-1111,26270.75,184798.82 ,10001/8888-1112,0,-158528.07 0048,10001/8888-1113,107859.88,754890.66 ,10001/8888-1114,0,-647030.78 0051,10001/8888-1115,17814.65,126728.16 ,10001/8888-1116,0,-108913.51 Grand Total,,151945.28,151945.28 code I have so far, but not making any headway as I don't understand some of it. received code from another forum Dim vClm As Variant Const coAccountString As Variant = "Acct" Dim coP As Integer Const coOutD As Variant = "Output Data" Const coInD As Variant = "Input Data" Dim coTempCC As String Dim totallines As String For vClm = 1 To totallines If (Round(coInD(vClm, 3), 2) < 0) Or (Round(coInD(vClm, 4), 2) < 0) Then If coInD(vClm, 1) < 0 Then coTempCC = coInD(vClm, 1) coP = coP + 1 coOutD(1, coP) = coTempCC coOutD(2, coP) = coAccountString coOutD(3, coP) = coInD(vClm, 2) coOutD(4, coP) = coInD(vClm, 3) coOutD(5, coP) = coInD(vClm, 4) End If Next vClm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
from pivot table to vb array copy to new table
I changed
Dim totallines As string to Dim totallines As Double and the Immediate window doesn't give me an error any longer So I tried this with my data and NOTHING happens... Do you have enough info to help me or do you need more? - g |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table or array formula | Excel Programming | |||
Use of Array Pivot Table for Percentile Calculation ... | Excel Worksheet Functions | |||
How do i convert row array into one column using pivot table | Excel Worksheet Functions | |||
Creating Array from Pivot Table | Excel Worksheet Functions | |||
Creating a Pivot Table directly from an Array | Excel Programming |