Transferring between workbooks

[dpaton05]

I have a complex spreadsheet that is used to generate a quote. The quote has information at the top: Caseworker, Organisation and Child/YP in 3 different cells. There is then a table under that called npss_quote. The table has rows relating to the information at the top.

The quotes then need to be copied to a another spreadsheet. This spreadsheet is called Costing tool. In Costing tool there is a table called tblCosting which contains more details regarding the quotes. The rows from npss_quote need to be copied below rows that exist in tblCosting. The information at the top of the quoting spreadsheet, Caseworker, Organisation and Child/YP needs to be the same for every row that gets copied across but the rows in npss_quote will have information that is specific to the individual row. The specific information for each row is Date, Service and Price.

I need code to be run from within the quoting spreadsheet to transfer the rows across to costing tool. I could work a lot of it out myself but I am not sure about copying the 3 cells at the top of the quoting spreadsheet to be the same for every row in npss_quote but then to have specific information regarding each row. The information that is specific to each row is Date, Service and Price.

So, every row that is copied from npss_quote to tblCosting will have the 3 cells at top of the quoting spreadsheet: Caseworker, Organisation and Child/YP, the same for each row. Every row in tblCosting will have a Date, Service and Price that is specific to each row in npss_quote.


The 3 cells at the top of the quoting spreadsheet that are to be copied for every row are:

• Caseworker in B6
• Organisation in B7
• Child/YP in in a merged cell G6:H6

These need to be copied for each row that is copied from npss_quote to tblCosting. The cells in tblCosting that they need to go in are:

• Caseworker needs to be put in column G
• Organisation needs to be put in column F
• Child/YP needs to be put in column D

The location of information that is specific to every row, Date, Service and Price is as follows:

• Date is column A of npss_quote and needs to go in column A in tblCosting
• Service is in column B of npss_quote and needs to go in column E in tblCosting
• Price is in column H of npss_quote and needs to go in column H in tblCosting

The header row for npss_quote is in row 10 with data starting in row 11. The header row for tblCosting is in row 4 with the data starting in row 5.


I have tried to explain this but if it doesn't make sense, please reply to me and ask for clarification.




I would just like to say that this forum is the best,
I would really appreciate help with this,

Dave

 

[dpaton05]

Re: Transferring between documents

Could someone help me with this problem please?

 

[mumps]

Re: Transferring between documents

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[dpaton05]

Re: Transferring between documents

I asked my supervisor and he said he would prefer if I didn't upload it so I think the best way to do it is to try and identify on on piece of the puzzle at once instead of just overloading everyone with all the info at once.

So there is a quoting spreadsheet that has 3 fields, followed by a table. The table has x number of lines and each line is relating to the 3 fields above it. Following several inputs, the spreadsheet provides accurate quoting prices for services after referencing data in the background. The 3 fields are Case worker, Organisation and Child/YP and each line in the table is relating to those 3 fields.

I know how to get the data copying from one spreadsheet to another but I don't know how to copy it from one table to another, but with the 3 fields at the top of the quoting spreadsheet to be the same for each row in the table on the quoting spreadsheet.

 

[dpaton05]

Re: Transferring between documents

Here they are, I need the code to run from the "send to costing tool" button on the first sheet in the quoting tool and for them to transfer to the costing tool.

https://www.dropbox.com/s/a7gvr3rcy59qwbn/Quoting tool.xlsm?dl=0
https://www.dropbox.com/s/x8uoe30fz5rmdir/Costing tool.xlsm?dl=0

Thanks for helping me Mumps

 

[mumps]

Re: Transferring between documents

For clarification:
You click the "Add Line" button which inserts a new blank row. Next you enter the data in that row in columns A:B and D:G. Then you click the "send to costing tool" button which copies the relevant data from npss_quote to costing tool. The data is transferred one row at a time. Is this correct or do you enter multiple new rows and transfer all the new rows starting at row 11 all at one time?

 

[dpaton05]

Re: Transferring between documents

For clarification:
You click the "Add Line" button which inserts a new blank row. Next you enter the data in that row in columns A:B and D:G. Then you click the "send to costing tool" button which copies the relevant data from npss_quote to costing tool. The data is transferred one row at a time. Is this correct or do you enter multiple new rows and transfer all the new rows starting at row 11 all at one time?

That is correct. Add line adds a new line and delete line removes one. The data already in it is only test data.

• He wants to enter the caseworker, organisation, locality and child/YP in the data entry fields above npss_quote. All of these details relate to a single quote.
• He then enters as many lines as the quote will require by clicking add button. These will be for different services provided on different dates.
• Data will be entered one row at a time then select add row or after all rows have been added. Not sure if you need to know this part, but just letting you know that the adding of rows may occur at the start or in between writing in each row.
• Once all the rows for a quote have been entered, my supervisor will review the totals and the total inc. GST for the quote.
• If he is not satisfied that it is correct, he wants to go back and review the entries to see where the error is.
• If he is satisfied that it is all right, he wants to press send to costing tool and have all the rows in npss_quote copied under anything in tblCosting. There could be any number of rows in npss_quote before the transfer and he wants to be able to transfer them all at once.
• When a quote is finished and sent, it will be for one instance of caseworker, organisation, locality and child/YP, which is at the top of the quoting tool, above npss_quote. Rows in npss_quote will be the breakdown of the quote for the caseworker, organisation, locality and child/YP. For every row that is copied with one click of send to costing tool the caseworker, organisation, locality and child/YP will be the same for each row.
• In the npss_quote, all new rows start at row 11. The header row is in row 10.
• For each row in npss_quote, data will be entered in columns A:G
• There is other columns in tblCosting but they will be for filling out data at a later date relating to the quote.

On a side note, do you know why this file has become so big in file size as it used to be roughly 200kb?


I hope I have fully explained this.

Thanks for your help Mumps,
Dave

 

[dpaton05]

Re: Transferring between documents

On a side note, do you know why this file has become so big in file size as it used to be roughly 200kb?

Size back to normal now, not sure what was going on there.

 

[mumps]

Re: Transferring between documents

Here are the links to your 2 files:
File1
File2
I've had to move the buttons and some comments in the Costing Tool to the top of the sheet otherwise they would have interfered with the data that is copied from the other file. I hope that is OK.

 

[dpaton05]

Re: Transferring between documents

.