from pivot table to vb array copy to new table

  • Thread starter Thread starter greegan
  • Start date Start date
G

greegan

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
 
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
Click to expand...
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.
 
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

The structure of your data and what you are trying to do are not clear
which will limit the replies you get.
Click to expand...
More explanation and the code you have tried might help.

This is what I have now
Click to expand...



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