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]

Something else I noticed was when I transfer rows to a blank costing tool, everything lines up now but it leaves a blank line at the top of the costing tool. If you could help me with that and the sorting problem I mentioned in my last post, I would really appreciate it.

Thanks Mumps,
Dave

 

[mumps]

Click the links below for your files:
https://app.box.com/s/qq8utry11a9hkt4d5aw0o6xi67p1tuz6
https://app.box.com/s/xm0ak5sreba4cp7j5ivw1hddek5yx68z

 

[dpaton05]

Thanks Mumps, just a few more issues I am having. Is it working for you as it is still transposing columns E and H for me and it is not keeping the rows together. By that I mean that even with the files in the previous post, in the quoting tool, the entry on the 3/7/18 is a supervised contact but when it is sent to the costing tool, the service for the 3/7/18 is youth work support.

If I delete all the rows in the costing tool and delete all rows except 1 in the quoting tool and try and transfer it, the row gets transferred again as it was before, with columns E and H being transposed.

Basically, the rows will be copied but the rows are not all lining up correctly.

I am sorry to be a pain Mumps,

Dave

 

[mumps]

Have a look at the attached file. This is what I get with the 2 files I last uploaded. The data seems to be in the correct cells. If I'm missing something, please indicate the cells that are not right.
https://app.box.com/s/rnktjirgkxeuc33sdfwpru7pcc40ir1k

 

[dpaton05]

This is so weird. I redownloaded the files again just to check and this is an image of the costing tool after the transfer https://www.screencast.com/t/D1upXJxNP9 Here is what the rows looked like in the quoting tool that were transferred https://www.screencast.com/t/xYFyzooN Could it be the settings or something similar?

 

[dpaton05]

Maybe you should tell me how to do it and I will try and make it on my end?

 

[mumps]

I think I found the problem. Columns I and J in Costing have formulas in them so the code I was using to find the last used row was taking the formulas into account. Replace this line of code:

lastRow2 = desWS.cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

with this line:

lastRow2 = desWS.Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

and give it another try.

 

[dpaton05]

I did that but it still wouldn't work. When I deleted all the formulas I have in the row it did work however. The only problem is that I need the formulas. I not only have formulas in columns I and J, but for every row in the costing tool, I have formulas in columns I, J, Z, AA, AC, AD, AG, AH, AI, AJ and AK.

Not sure if you need to know this but I have formulas for each row in columns H, I, J, K and L in the quoting tool too.

Thank you so much for helping me with this mumps,
Dave

 

[mumps]

I'm having some problems with my computer. Please upload the most recent versions of your 2 files including the formulas and I'll get back to you as soon as I can.

 

[dpaton05]

This is getting very weird Mumps. A few more issues:

1. This is very strange, I didn't change anything and it now appears to be copying into the correct line but it copies everything correctly except the date to the costing tool.
2. I have also noticed that when I try to add or delete a line on both workbooks using the delete and add buttons, it is not very fast. Maybe as the code I have used is slowing it down??
3. I need a button on the costing tool that will delete everything in every row with one click, but obviously, to leave the formulas so when you go to do a new quote, the formulas will be there.
4. When I type in several lines in the quoting tool and copy them, they appear to mostly copy correctly, except the date. But if I change the entries in the quoting tool and try and copy them, it overwrites the entries in the costing tool, instead of putting them under it. In most circumstances, a new quote in the quoting tool will be new costing in the costing tool (which is why I wanted the ability to delete all the rows at once instead of individually). But in case something is forgotten, I wanted the ability to add several rows under the information already in the costing tool.
5. The date entered on the quoting tool does not always trigger the alert of it being earlier then the current date, even if it is.

Here are the files,
https://www.dropbox.com/s/ke57wg2zv1knzqt/Costing tool.xlsm?dl=0
https://www.dropbox.com/s/muyhhvhbd6icjzq/quoting tool 5.2.xlsm?dl=0

Thanks again for this Mumps,
Dave