Macro to send data to estimate sheet

[STU22]

Document1.pdfBook1.xls

I would like some help with a macro to send data from the MPD sheet to the Estimate sheet (I forgot to name the sheets, sheet 1 is going to be MPD and sheet 2 will be Estimate)
Im running excel 2003
I have attached a pdf and a file to show what I want to do.
Help would be greatly appreciated. Thanks

[xladept]

Hi STU,

Here's a start - I renamed Sheet1 to MPD and I pasted the first ten rows of Sheet2 into Sheet3:

Please Login or Register  to view this content.

[STU22]

Hi xladept,
Thanks for taking a look at this.
I don't think I was very clear on some things.
If possible I only want to send data to the estimate sheet that has a number in the Qty. column.
Then after sending data the cells would return back to blank.
Also, a line added to the bottom of each different Area, and Bold Area Name at the top of each different Area with the area total in Bold Text as shown in the attached pdf.

[xladept]

The data doesn't transfer unless there is a value in the Quantity line because, in your sample, those data are all indicated by a Room Number in the Area column. But, I'll take another look. As I said. its a start

[stnkynts]

Give this a try. I hope you do realize that most businesses actually pay someone to do this for them.

Please Login or Register  to view this content.

[STU22]

Hello stnkynts,

Thank you for the help. I do realize that we are fortunate to have people like you on this forum to help people like me. I really appreciate it.

I tried to run this code and got a Compile error: (Method or data member not found). It highlights the first row in Yellow " Sub EstimateGenerator()" It also highlights .Sort in blue on the first line right under 'Sort Estimate Sheet

Im running 2003 not sure if this matters or not?

[STU22]

xladept,

My apologies I didnt realize that it was set up to only transfer data if the Quantity column had a number in it. When i ran the code it transferred the rows with the bold text headings ie. -UPPER CABINETS-,-BASE CABINETS-etc. and I dont believe there is any data in those row, not supposed to be anyways.

I have it set up when I enter a number in the Qty. field it populates the area field with the area name.

Thanks for your help.

[stnkynts]

Book1-done.xls Works just fine for me. See attached workbook. Enable macros, run the macro.

[STU22]

Thanks stnkyts,

I tried the attached workbook and got the same error as before.
Is there a setting or something that I need to change to get this to run, Im anxious to run this.
Are you running 2007? Im running 2003 does this matter?

[stnkynts]

Im running 2010 at the moment. But the file is saved as a 2003 compatable file so I think it should work. If there is something with your 2003 version that is causing it to error, I am afraid you are "out of luck".

Edit: (Shot in the Dark)

Try:

Please Login or Register  to view this content.

[STU22]

Thanks stnkynts, I tried it on my laptop and it works. I'm still testing it and found a few quirks nothing major I hope.

1. In cells G10:G24 I set the column format as a number and after sending to estimate these cells formate returns to bold and currency. I need G to be number Format.
2. I tried to send data without anything in the G column and it would not run, I would like if there is nothing in column C then if there is any data in the row it would not be sent.
3. Every time I send to estimate it pastes the data over the existing data, I would like to add to it and continue to sort per area.

I really appreciate the code you did for this, very smooth.

[xladept]

Hi Stu,

Here's mine - it will add to whatever remains on the Estimate sheet:

Please Login or Register  to view this content.