I have been racking my head around this and several suggestions were very
close, but I could not get them to work...

I have a user interactive excel session and want to dynamicly collect parts
list, create an order sheet and invoice.

I have a parts list?
Col_A Col_B
widgit A Y
widgit B
widgit C Y
widgit D

I know I need to build an array and index thru and remove the blanks and
nulls...

Desire result, somewhere on the same worksheet:
Order
widget A
widget D

That is it, pretty simple, but I have yet to figur this out.

Hi,

You might like to try the following.

Use AutoFilter. I dont think it is the greatest way of creating invoices
but it works and if you want to keep a copy of the invoice then a macro could
be used to copy it to another worksheet or even another workbook. (It can
also be copied manually)

Place column headers on say row 11.
Click in cell A12 and freeze panes so that you now have rows 1 to 10 to
insert your own business details and customer details.
Insert all of your products and any other info re price and number ordered
etc and formula for calculated cost for each item in the table.
At bottom of list use SUBTOTAL function to get all the grand totals. (You
can leave 1 or 2 rows blank between the table of data and the subtotals.)
Lookup SUBTOTAL in help for all the math options of Sum, Count, Average etc.
Click somewhere in the table and set AutoFilter.
Now insert the number ordered against each item. (I think that this is
preferable to Y and it is specific on an invoice)
Click the dropdown on the number ordered and select Custom (or with xl2007
select Number filters) and set to Greater than 0 (Zero).

Hey presto you have your invoice ready to print. (The drop down arrows do
not print.)

It is quite easy to create a macro attached to a button on your worksheet to
toggle the AutoFilter between show all and greater than zero on number
ordered.

You will need to clear the number ordered data before creating a new
invoice. This could be included at the end of a copy to another worksheet
macro.

1. If you want the macros then reply but I need to know:
2. What column has the number ordered (counting from the left of the filter
column headers).
3. If you want the copies in another worksheet same workbook or in another
workbook.

--
Regards,

OssieMac


"thayes" wrote:

I have been racking my head around this and several suggestions were very
close, but I could not get them to work...

I have a user interactive excel session and want to dynamicly collect parts
list, create an order sheet and invoice.

I have a parts list?
Col_A Col_B
widgit A Y
widgit B
widgit C Y
widgit D

I know I need to build an array and index thru and remove the blanks and
nulls...

Desire result, somewhere on the same worksheet:
Order
widget A
widget D

That is it, pretty simple, but I have yet to figur this out.

Thanks, but i not sure I understand your freeze frame option, but I see where
it is going and not quite where i was going with this project! Thanks!

I am interested in the macro, but would rather have some mthod or function
that determines selected widgets, my case col G (I will be pulling several
fields from that row) and writing out to a 15 rowed Invoice, all the
totalling is already done, just trying to have a clean list of selected items
on the Invoice.

Tom

"OssieMac" wrote:

Hi,

You might like to try the following.

Use AutoFilter. I dont think it is the greatest way of creating invoices
but it works and if you want to keep a copy of the invoice then a macro could
be used to copy it to another worksheet or even another workbook. (It can
also be copied manually)

Place column headers on say row 11.
Click in cell A12 and freeze panes so that you now have rows 1 to 10 to
insert your own business details and customer details.
Insert all of your products and any other info re price and number ordered
etc and formula for calculated cost for each item in the table.
At bottom of list use SUBTOTAL function to get all the grand totals. (You
can leave 1 or 2 rows blank between the table of data and the subtotals.)
Lookup SUBTOTAL in help for all the math options of Sum, Count, Average etc.
Click somewhere in the table and set AutoFilter.
Now insert the number ordered against each item. (I think that this is
preferable to Y and it is specific on an invoice)
Click the dropdown on the number ordered and select Custom (or with xl2007
select Number filters) and set to Greater than 0 (Zero).

Hey presto you have your invoice ready to print. (The drop down arrows do
not print.)

It is quite easy to create a macro attached to a button on your worksheet to
toggle the AutoFilter between show all and greater than zero on number
ordered.

You will need to clear the number ordered data before creating a new
invoice. This could be included at the end of a copy to another worksheet
macro.

1. If you want the macros then reply but I need to know:
2. What column has the number ordered (counting from the left of the filter
column headers).
3. If you want the copies in another worksheet same workbook or in another
workbook.

--
Regards,

OssieMac


"thayes" wrote:

I have been racking my head around this and several suggestions were very
close, but I could not get them to work...

I have a user interactive excel session and want to dynamicly collect parts
list, create an order sheet and invoice.

I have a parts list?
Col_A Col_B
widgit A Y
widgit B
widgit C Y
widgit D

I know I need to build an array and index thru and remove the blanks and
nulls...

Desire result, somewhere on the same worksheet:
Order
widget A
widget D

That is it, pretty simple, but I have yet to figur this out.

Hi Tom,

Freeze panes basically freezes the top rows or left columns (or both) so
that they never scroll off the screen.

When Freeze panes is applied, the rows and/or columns are frozen above and
to the left of the current cell selection. If you only want to freeze rows at
the top then select a cell in column A (say A11) and apply Freeze panes and
then rows 1:10 will be frozen against vertical scrolling. (always visible)

If you want to also freeze say column A because it has row identifier info
in it then select say B11 then Rows 1:10 are frozen against vertical scroll
and column A is frozen against horizontal scroll.

If you look up Freeze panes in Help you should find all the info you need.

As some added extra info for the future, it is always a good idea to include
in your post the version of xl you are using. Sometimes it does not matter
but other times it is critical when explaining how to do something.

--
Regards,

OssieMac

Thanks, if you must know, this is my very first post to this forum! Thanks!

i am running xl2007 and i am in the middle of writing a very simple macro
that will run on re-calc of value change (my count of selected); just about
98% done in my test application.

"OssieMac" wrote:

Hi Tom,

Freeze panes basically freezes the top rows or left columns (or both) so
that they never scroll off the screen.

When Freeze panes is applied, the rows and/or columns are frozen above and
to the left of the current cell selection. If you only want to freeze rows at
the top then select a cell in column A (say A11) and apply Freeze panes and
then rows 1:10 will be frozen against vertical scrolling. (always visible)

If you want to also freeze say column A because it has row identifier info
in it then select say B11 then Rows 1:10 are frozen against vertical scroll
and column A is frozen against horizontal scroll.

If you look up Freeze panes in Help you should find all the info you need.

As some added extra info for the future, it is always a good idea to include
in your post the version of xl you are using. Sometimes it does not matter
but other times it is critical when explaining how to do something.

--
Regards,

OssieMac

Was this not close to what you wanted?
Simon Lloyd;269845 Wrote:
This should do what you need:

Code:
--------------------
Dim rng As Range
Dim MyCell As Range
Set rng = Sheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng
If LCase(MyCell.Offset(0, 1).Value) = LCase("Yes") Then
Range(Cells(MyCell.Row, 1), Cells(MyCell.Row, 2)).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=75202