from pivot table to vb array copy to new table

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
 
M

ManicMiner17

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

greegan

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

greegan

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
 

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

Top