Automatically Populating Worksheet from Collected Input for Pricelist
 

I have a pricelist/configurator helper workbook that I have produced in Excel with 12 worksheets (8 visible/4 hidden). My sales people enter nummeric data in columns matching the item they want in their proposal in each of the 8 visible sheets. Each sheet contains a different part of the product mix being proposed. The inputs are automatically copied in cells in the hidden sheets where the pricing function occurs. The inputs are also copied to the last visible worksheet that is the "pricelist" for our proposal.

Here's the issue.

After the input of each proposal is complete, the salesperson has to manually delete any rows that contain a zero quantity in the last worksheet (the "pricelist'). I have seen other Excel spreadsheets that automatically insert the rows that have numeric data in them in a logical fashion. I have never pursued how to do it but our proposals are now large enough to cause this to be a MAJOR inconvenience to the sales reps. I would like to have the last sheet in the workbook automatically populate with data from the other worksheets that have a numeric quantity associated with them.

Is there a plausible way to do this without being a Jedi Master of VBA?

--
Message posted via http://www.officekb.com

Hi David

just an idea, you say
"The inputs are also copied to the last visible worksheet that is the
"pricelist" for our proposal." ... why don't you test the quantity at this
point and only copy them if the quantity is 0.

alternatively, have a macro that sorts the pricelist for your salesreps,
puting the empty quantities at the bottom, or one that deletes the lines for
the sales reps - this could be linked to a "Clean Up" button on the toolbar
which the sales reps could press to do the clean up - the code would be
similar to the following:
Public Sub DeleteBlankRows()
'This will delete all the blank rows if cell in column A (Columns(1)) is
blank within the active sheet

On Error Resume Next
Intersect(ActiveSheet.UsedRange.EntireRow,
Columns(1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

(found on a post by Ken Wright)

hope this helps
Cheers
JulieD


"David Littrell via OfficeKB.com" wrote in message
...

I have a pricelist/configurator helper workbook that I have produced in
Excel with 12 worksheets (8 visible/4 hidden). My sales people enter
nummeric data in columns matching the item they want in their proposal in
each of the 8 visible sheets. Each sheet contains a different part of the
product mix being proposed. The inputs are automatically copied in cells
in the hidden sheets where the pricing function occurs. The inputs are
also copied to the last visible worksheet that is the "pricelist" for our
proposal.

Here's the issue.

After the input of each proposal is complete, the salesperson has to
manually delete any rows that contain a zero quantity in the last
worksheet (the "pricelist'). I have seen other Excel spreadsheets that
automatically insert the rows that have numeric data in them in a logical
fashion. I have never pursued how to do it but our proposals are now large
enough to cause this to be a MAJOR inconvenience to the sales reps. I
would like to have the last sheet in the workbook automatically populate
with data from the other worksheets that have a numeric quantity
associated with them.

Is there a plausible way to do this without being a Jedi Master of VBA?

--
Message posted via http://www.officekb.com