Creating a proposal

[mdolinger]

I have a workbook that can have 3-15 worksheets that compile into a single summary worksheet for bidding purposes. I would like to take bid sheet and turn it into a proposal that has the same header, then the bidding items, then a signature section. I've attached a sample of my workbook. On the attached worksheet, I use the bid sheet to compile the individual sheets to one nice summary. I'd like to take the summary and make a nice looking proposal. I tried using relative references, but it's not the ideal solution. I'm left with a bunch of empty space and need to have 2 pages regardless of how many bid items I have. I'd like to be able to use a macro that I can push and create said proposal that doesn't have all the blanks and can handle a workbook with 1 bid item or 20 bid items and anything in between. I'm trying to learn how to get this to work and have been playing with VBA, but I'm a bit out of my excel range.

Thanks in advance for the help!

[dflak]

Do you want to go from the individual sheets to the bid sheet, or do you do the bid sheet manually? Are you just interested in taking what is on the Bid sheet, regardless of how it is calculated, and move to the proposal sheet?

If you want to get the information from the sheets to the bid sheet, I have a couple of questions: It looks like you are looking for values associated with the following two key words: DIR COST - BOND and D COST - B & INS.

Are there any other Keywords?

Also on the bid sheet, How do you determine which sheets to use?

[mdolinger]

dflak, thanks for replying. The way I have the workbook set up it that the Index page has a few cells that populate in other sheets, specifically job name, job location, job number and the wages. I have named those cells so I can easily use them in other sheets. I then take the DIR COST-BOND in each individual sheet to the cost cell in the corresponding row for the specific repair. Each job is different in how many sheets I will need or use. For most jobs, I have 3-5 different worksheets that I use but larger jobs can have upwards of 20 sheets, so unfortunately there is no consistency there. I guess I'm just interested in moving what I have in the Bid sheet over to the proposal sheet, but I don't need all the columns over. I don't need to have unit cost, cost or markup on the proposal. Does that make sense? thanks again.

[dflak]

The program has the following features. It creates the line entries on the proposal according to the bid sheet. If there are enough items on the bid sheet that the disclaimer spans the page break, the disclaimer is pasted either at the top of the second sheet, or at the end of the items if they spill over onto the second sheet. I did not get more sophisticated than that. I did not test to see if the disclaimer spans sheets 2 and 3. If it does, you can move it down manually. It's a picture.

I converted the Bid Sheet to an Excel Table for a couple of reasons. Excel tables copy down formulas automatically (easy for you) and tables know how many rows they have (easy for me).