Automatically Populating Worksheet from Collected Input for Pricelist

[David Littrell via OfficeKB.com]

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?

 

[JulieD]

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