I’m working on a spreadsheet for someone who runs an engineering company and the four owners quote for jobs, then agree the quote and send it on to the customer to then see if its agreed.

Currently they all save and use one spreadsheet so recreating information and storing it god knows where. It’s also extremely basic so I’m working on something more functional and visual appealing that they can also then send on to customers.

I’ve created the intial booking page and my thoughts are the quotes then move on to a preliminary page which they look at together, agree on and then copy on to a historical quote which they can then reference at any point in the future for pivot tables or to analyse based on company.

Where I’m struggling is getting the information copied from the initial page to the preliminary page and using the check boxes too. I got it working intially but it then replaces the information on the same row each time I do it. I dont want the users to have to go and move their information. I would also like the check box to be copied to the page with the name of who completed the quote.

Maybe someone can suggest some other ideas for how I could do this?

[url=https://flic.kr/p/P7mVY6]intialquote[/url] by Alison Clarke, on Flickr

What do you have behind the scenes? Assume you are using macros?

Its the sort of thing where possible a db starts making sense instead but will depend on available infrastructure.

Big Red Button and a Macro?

I’ve got something here which starts with a template but once you press the button it generates a copy with the info entered as a read only file i a second directory. You could do that to generate a customer facing and records quote file

Yeah for the customer copy I was going to do exactly that a button which generates a read only copy for customers in a second directory. I’d be interested see that coding if you wouldn’t mind sharing.

Could almost do something similar but then there would be 4 files each time with their quotes on it which would need selecting one of them and adding them into a history directory. I was hoping this would be a one solution.

So far I have this working to copy the information across but I dont know what code to add to get it to work on the next blank row each time rather than a specified cell.

Sub test()
‘
‘ test Macro
‘

‘
Range(“D3,D6,D9,D12”).Select
Range(“D12”).Activate
Selection.Copy
Sheets(“Preliminary Quoting”).Select
Range(“B3”).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Sheets(“COMPLETE ME”).Select
Range(“F5:J5”).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“Preliminary Quoting”).Select
Range(“F3”).Select
ActiveSheet.Paste
Sheets(“COMPLETE ME”).Select
Range(“F8:J8”).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“Preliminary Quoting”).Select
Range(“K3”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(“COMPLETE ME”).Select
Range(“F11:I11”).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“Preliminary Quoting”).Select
Range(“P3”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Sheets(“COMPLETE ME”).Select
Range(“F14:G14”).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“Preliminary Quoting”).Select
Range(“T3”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I assume you are getting paid to do this ?

You need something like this to find the last row with data:

RowCount = Cells(Cells.Rows.Count, “C”).End(xlUp).Row ‘ get last row of data

Richmars, which point of the code would you insert that line?

Excel gives me the cold sweats. It’s the stuff of nightmares.

I’m one of those sado’s that loves it. I have all the information moving across now and formatting done to the new row but I don’t know how to make it work the next time without overrighting that info…. unless I could do it a stupid way and have another button that will make that work

Richmars, which point of the code would you insert that line?

Not overly clear from your code but its going to be for the target sheet which I think would be your prelimary sheet.

If correct since you copy into it several times you would want the row count stored off once at the beginning and then use it in each.

Replace
Range(“K3”).Select

with

Cells(RowCount,11 ).Select

and so on

You put that line just before the point you need to know the last row.

(I’m no expert, I just search the internet for something that does similar to what I want)