Excel : Copy row into other page if a field is not empty

[frontside]

Where using excel as an orderbook where the items we sell are divided into different pages. Is there a way to copy only the items with >0 in the PCS (how many items) field (on alla pages) into a summery page?

I could use a filter on every page showing only the items with something in the PCS field but theres a lot of manual work and the users aint all that computer friendly.

I guess you have too loop every page and copy the rows I want using vb? have anyone done something similar out there?

//Thanks

Mike, Sweden

 

[DPlank]

You wouldn't need to use VBA for this, though it would certainly allow you to achieve your goal.

On the summary page, show all the part names/numbers etc and use the COUNT function to total up the numbers for each part.

Depending on how your data is structured, SUMPRODUCT or COUNTIF may be better options for the formula.

Then apply your filter to the summary page on the PCS field.

Is that what you are trying to achieve?

Cheers,
Dave

"Yes, I'll stop finding bugs in the software - as soon as you stop writing bugs into the software." <-- Me

For all your testing needs: Forum1393

 

[frontside]

Thanks for the quick response,

What I´m trying to do is copy a full row if a field is not null and display them on a nother page. ex

Page 1
Art pcs
10 0
20 7
30 0

Page 2
Art pcs
60 1
70 0
80 0

SummaryPage (show the rows where the pcs is >0=
Art pcs
20 7
60 1

I´m not that good at Excel (working more with Access), So I need some Newbie treatment on the answers


Mike, Sweden

 

[SkipVought]

I'd make a new column with a formula to identify thos rows with pcs > 0
[tt]
=if(pcs>0,1,0)
[/tt]
Then use Data > PivotTable... with the new field in the PAGE FIELD - Select Pivot Item = 1.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[frontside]

Thanks for the tip about pivot and I did manage to get it to work with one page but not if I have several pages.I Read somewhere that U cant use text (only numeric values and I have several fields with text like the description of the items etc.) in multible source Pivot? is that true?

Any other ideas or do I have to solve this using VB and if thats the deal does anyone have a sample code for looping through rows and pages and copying those rows with a pcs value to a summary page.

Would love some ideas.

//Mike

 

[SkipVought]

The Pivottable is your SUMMARY items (newField=1)


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

For VBA issues, please post in the VBA forum: Forum707

Do some searching first - there will be countless examples of code ued for looping and copying..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question