Carry Rows Over to New Sheet (Under Only Specific Circumstances)

[smartmika]

Howdy, cyberspace!

I have a rather complex Excel issue that I'm hoping one of you might be able to help me figure out. I'm a student working part-time for a small Mom & Pop construction company, and I'm trying to get them to cross over to the dark side of submitting all of their paperwork electronically rather than filling out identical forms by hand and faxing them. I've put together a sales document that we can use to price various installation items that will generate automatic totals based on inputting square footage, number of items, etc.

I'm now trying to figure out if and how I could add a sheet to the document so that it automatically pulls line items from the original sheet and adds that entire line to the new sheet--but without inputting columns involving pricing or points. We need a separate sheet that is essentially a summary of the first sheet to e-mail to the customers. It should include only items where data were entered on the first sheet, but it should omit line-by-line pricing and include just a grand total. Right now, we're currently writing the breakdown out by hand... but I think I could help our company be much more efficient. Because it's hard to explain this theoretically, I've attached a copy of the spreadsheet.

Here's a concrete example using the attached document: Let's say we have a customer who is interested in getting a quote for "haul away refrigerator" (Line 43), and they want to have one "wall cabinet" (Line 68) installed. Is there a way to have these lines automatically transferred to the new sheet (labeled "Cust. Copy") along with the category headings (PRE-CONSTRUCTION, DEMOLITION, REMOVAL, AND HAUL AWAY and PER-BOX CABINET INSTALLATION [Lines 36 and 66])--if and only if it is filled out on the first sheet? Furthermore, can we have it transfer the lines with only the "item description" (Column A), quantity (Column E), and notes (Column H) included?

Thus, on the second sheet ("Cust. Copy") it would include only:

PRE-CONSTRUCTION, DEMOLITION, REMOVAL, AND HAUL AWAY [category heading]
Haul away refrigerator 1 [notes]

PER-BOX CABINET INSTALLATION [category heading]
Wall cabinet installation 1 [notes]


The other category headings and line items would not be transferred, as they would be blank.


I have a feeling that there *has* to be a way to accomplish this, but my knowledge of Excel isn't sophisticated enough to pull it off without guidance. If any of you out there are willing to try to tackle this, I would be tremendously, ridiculously grateful! I've tried to explain this as straightforwardly as possible, but I'd be happy to clarify any ambiguities that might remain.

Thank you very, very, very much for your assistance!!

Michael

[bebo021999]

Try it in attachment

[mrice]

Try this macro. It's pretty basic in terms of formatting but should get you started.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

[smartmika]

Thank you guys so much for your help!

MRICE: That macro is fantastic--and I'm excited to use a macro for the first time in my life! However, it doesn't seem to update in real time (i.e., the "cust. copy" sheet doesn't change if I add or remove line items from the "kitchens" sheet). Do you have to re-run the macro every time you revise the "kitchens" sheet?

BEBO: I really appreciate your formatting! However, I noticed that it only changes up to a certain number of line items--and then it won't let me add any more. It's quite possible that we'll end up using 40 line items per customer. How would I go about extending the formatting so it will update at least 40 of the rows from the "kitchens" spreadsheet?

Again, these both seem like fantastic ways to tackle this issue... my hope that this could be accomplished was wavering--but I should have known you guys would know how to figure it out. Thank you again!!!!!



Michael

[mrice]

For auto updating, add the following code to the Sheet1 tab in the VBA editor

Please Login or Register  to view this content.

[mrice]

On reflection, this is better.

In Module1 paste

Please Login or Register  to view this content.

In Sheet4 paste

Please Login or Register  to view this content.

[smartmika]

MRICE: That macro is *exactly* what I was looking for! I'm genuinely in awe of what you were able to create for me... thank you, thank you, thank you!!

I suppose I do have one more question for you: I realize now that I'm not able to add any text to the page with the macro. Is there any way I could add Rows 1, 2, 3, 5, 6, 7, 8, 9, and 10 to the top of the page controlled by the macro? Also, is there any way to add a "notes" section to bottom of that sheet--or might I be able to add a notes row to the bottom of the "kitchens" sheet that is then carried over to the "Cust. Copy" sheet?

Again, you have *no* idea unexpectedly delightful you have made my day :-) Thank you!

Michael

[mrice]

Try this variation.

Please Login or Register  to view this content.

I've put the additional notes row on the Kitchen sheet in row 310. I've also set the print area on the assumption that you may want to print the customer sheet.