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]

Thanks for this Mumps, just a few issues.

1. I have 2 entries in the your QT file and I try and send it to the costing tool and it copies but it does so not as appears in the QT file. This occurs when I delete all the rows in tblCosting so there is nothing in there. If there is rows already in there, it appears to work fine.
2. There are 3 rows in the costing tool after the transfer from 2 rows in the QT file and it appears like the first and third rows have been transposed.
3. The first line in the costing tool has nothing in columns A, D, F and G but it correctly shows the data for E and H
4. The second line in the costing tool appears to copy correctly
5. The third row has data in A,D, F and G but nothing in E and H. This is the exact opposite to the first row.
6. If you press the copy button again it copies the data but appears to be missing columns E and H
7. If I just have one row in the QT file, it copies into 2 rows with the above columns being transposed.
8. What things did you add or change so I can add them in the file that has all my data and most importantly, so I can learn how you did it?

Thanks Mumps,
Dave

 

[mumps]

My apologies. I forgot to mention that I had to make the headers in B10 and H10 in the NPSS_quote_sheet match the headers in E4 and H4 of the costing tool in order for the macro to work properly. If you are using the macro in different files, then make those headers match as in the files I uploaded and try it again.

 

[dpaton05]

I changed the headers so they are exactly the same and it appears to work. By that, I mean that the rows copy correctly but they don't copy into the table, they are copied to just below the table. Therefore, this means that all the formulas I have in the table won't work on the copied rows.

 

[dpaton05]

I forgot I was working with my original file, not the one you created for me. I have changed the names of the headers to be the same but it will still split one row in the quoting tool to be 2 rows in the costing tool with columns E and H in row 1 and all the rest in row 2.

Thanks Mumps,
Dave

 

[mumps]

Try:

Private Sub CmdSend_Click()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim desWS As Worksheet, srcWS As Worksheet
    Set srcWS = ThisWorkbook.Sheets("NPSS_quote_sheet")
    Set desWS = Workbooks("Costing tool.xlsm").Sheets("Home")
    Dim lastRow1 As Long, lastRow2 As Long, i As Long, header As Range, x As Long
    lastRow1 = srcWS.Range("B" & srcWS.Rows.Count).End(xlUp).Row
    lastRow2 = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With srcWS.Range("A:A,B:B,H:H")
        For i = 1 To .Areas.Count
            x = .Areas(i).Column
            Set header = desWS.Rows(4).Find(.Areas(i).Cells(10), LookIn:=xlValues, lookat:=xlWhole)
            If Not header Is Nothing Then
                srcWS.Range(srcWS.Cells(11, x), srcWS.Cells(lastRow1, x)).Copy
                desWS.Cells(lastRow2 + 1, header.Column).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            End If
        Next i
    End With
    With desWS
        .Range("D" & lastRow2 + 1 & ":D" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("G7")
        .Range("F" & lastRow2 + 1 & ":F" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("B7")
        .Range("G" & lastRow2 + 1 & ":G" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("B6")
    End With
    With Application
        .CutCopyMode = False
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[dpaton05]

Even with the updated code, it still transposes every column except E and H by 1 row so for 1 row in the quoting tool file, it still copies that into 2 rows in the costing tool when it should be copying it to 1 row. Columns E and H are being copied to the first row, where everything should be copied but every other column copies to the next row below it.

 

[mumps]

I couldn't duplicate the problem when I tested the macro. Could you upload a copy of the file that is not working for you? De-sensitize the data if necessary.

 

[dpaton05]

Sure, here are the files:

https://www.dropbox.com/s/hjuxbqg1wfszr8i/Costing tool.xlsm?dl=0
https://www.dropbox.com/s/iij4cp3744jzq21/quoting tool 1.1.xlsm?dl=0

Thanks Mumps
Dave

 

[mumps]

Replace this line of code:

desWS.Cells(lastRow2 + 1, header.Column).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

with this line:

 desWS.Cells(lastRow2 + 1, header.Column).PasteSpecial xlPasteValues

 

[dpaton05]

I haven't had a good look at it yet but it appears to work, thanks. Just wondering how I could get the table, tblCosting, sorted by the date field in ascending order using vba. I wanted the table to sort after every transfer from the QT file to the costing tool.

Thanks Mumps,
Dave