Range Manupilation
 

one wb with several sheets containing a template for each product
Each template has three columns item#(empty rows) description(several
rows)and required quantity(empty rows). Description contains several
rows. This is a key factor
Since description is general you need to keyin the part# in the
required item. Example Screws are a general description but to order a
screw you have to look at the price list (the last sheet ) and pick the
correct screw and keyin the the item# and required quantity.

So, after you fill each template with the required items; you end up
with a list in each worksheet with partially completed lines.
Completed lines are lines that have the THREE fields Filled.

I need to create a summary sheet that contains ONLY the filled lines so
I can perform a Pivot Table on the filled lines.

Can you help me out

Range Manupilation
 

Dim sh as Worksheet
Dim rng1 as Range
Dim rws as Long
for each sh in thisworkbook.worksheets
if lcase(sh.name) < "price list" and lcase(sh.Name) < "summary" then
if application.CountA(sh.Range("A2:C2")) = 3 then
rws = sh.Range(Sh.Range("B2"), _
Sh.Range("B2").End(xldown)).rows.count
if rws 4 then rws = 1
sh.Range("A2").Resize(rws,3).copy _
Destination:=worksheets("Summary").Cells( _
rows.count,2).End(xlup)(2,0)
end if
end if
Next

--
Regards,
Tom Ogilvy


"Arishy" wrote in message
ups.com...
one wb with several sheets containing a template for each product
Each template has three columns item#(empty rows) description(several
rows)and required quantity(empty rows). Description contains several
rows. This is a key factor
Since description is general you need to keyin the part# in the
required item. Example Screws are a general description but to order a
screw you have to look at the price list (the last sheet ) and pick the
correct screw and keyin the the item# and required quantity.

So, after you fill each template with the required items; you end up
with a list in each worksheet with partially completed lines.
Completed lines are lines that have the THREE fields Filled.

I need to create a summary sheet that contains ONLY the filled lines so
I can perform a Pivot Table on the filled lines.

Can you help me out

Range Manupilation
 

A worm thank you for pointing me to the right direction.
You will not believe it, there was no single error It ran first time Mr
Genius.

"if application.CountA(sh.Range("A2:C2")) = 3 then"

It did not keep going to the rest of the sheet

I ran it on test data I only got the first line from each sheet. The
looping did not continue after A2.

I believe you picked 4 as an arbitrary max for column B where the
description of all items available for this product reside. Can I change
that to say 20 ( Max Possible for Column B)

You resized the range based on B2 while if you do it on column A you
will get number of rows I need to loop to finish each sheet. This of
course if I understand your logic.

Of course I will keep going but a chat will help a long way


*** Sent via Developersdex http://www.developersdex.com ***

Range Manupilation
 

I interpreted you description to be on item per sheet. That item would be
found in A2, B2, C2
If there were entries there, then B2 could have text in multiple rows below
B2

I put in the limit of 4 (it was arbitrary) in case you had only an entry in
B2 because that would go all the way to the bottom of the sheet.

So I would see your data

A2: Something
B2: Start of description
B3: Possibly More description
B4: Possibly More description
B5: Possibly More Description
C2: Something

that is what I made the macro look for and copy to sheet summary.

Apparently that isn't correct - but I don't know what is correct. Hopefully
you can adapt the code to your actual situation.

If not, send a small, sample, representative workbook to


Include all discussion to date. And perhaps include a sheet named "Sample
Results" with what the output would look like.

--
Regards,
Tom Ogilvy



"samir arishy" wrote in message
...

A worm thank you for pointing me to the right direction.
You will not believe it, there was no single error It ran first time Mr
Genius.

"if application.CountA(sh.Range("A2:C2")) = 3 then"

It did not keep going to the rest of the sheet

I ran it on test data I only got the first line from each sheet. The
looping did not continue after A2.

I believe you picked 4 as an arbitrary max for column B where the
description of all items available for this product reside. Can I change
that to say 20 ( Max Possible for Column B)

You resized the range based on B2 while if you do it on column A you
will get number of rows I need to loop to finish each sheet. This of
course if I understand your logic.

Of course I will keep going but a chat will help a long way


*** Sent via Developersdex http://www.developersdex.com ***