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

 

[mumps]

Could you upload an updated file with the changes?

 

[dpaton05]

https://www.dropbox.com/s/yiwgyixxf4run8n/quoting tool 6.0.xlsm?dl=0

I can't get the formula in H16 to work by summing GST for everything except activities. GST is 10% of total bill. Not sure if I have already told you this.

Thanks Mumps,
Dave

 

[dpaton05]

https://www.dropbox.com/s/yiwgyixxf4run8n/quoting tool 6.0.xlsm?dl=0

I can't get the formula in H16 to work by summing GST for everything except activities. GST is 10% of total bill. Not sure if I have already told you this.

Thanks Mumps,
Dave

Not sure what I did but I was fiddling around with it and got it to work. I think I renamed all of the formulas and the source cell from *Activities to Activities as I thought having the wild card in there may have affected it. It didn't change anything so I changed it back and then it worked. I think I did that but I am not sure. Thanks anyway Mumps.

 

[dpaton05]

Found another problem.

• If I now try and copy to the costing tool, I get an error message saying "Method ‘range’ of Object ‘_Worksheet’ failed". It still copies to the table but I want to get rid of the error message.

It highlights this code:

desWS.ListObjects("tblCosting").Sort.SortFields. _
        Add Key:=desWS.Range("tblCosting[Date]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal

If anyone knows how to code and could also help me debug this, here is the code from the whole macro:

Private Sub CmdSend_Click()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Dim desWS As Worksheet
    Dim srcWS As Worksheet
    
    Set srcWS = ThisWorkbook.Sheets("NPSS_quote_sheet")
    Set desWS = ThisWorkbook.Sheets("Costing_tool")
    
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    Dim i As Long
    Dim x As Long
    Dim header As Range
    
    lastRow1 = srcWS.Range("B" & srcWS.Rows.Count).End(xlUp).Row
    lastRow2 = desWS.Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With srcWS.Range("A:A,B:B,H:H")
        If lastRow2 < 5 Then
            lastRow2 = 5
            desWS.ListObjects.Item("tblCosting").ListRows.Add
            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, header.Column).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
                End If
            Next i
            With desWS
                .Range("D" & lastRow2 & ":D" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("G7")
                .Range("F" & lastRow2 & ":F" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("B7")
                .Range("G" & lastRow2 & ":G" & .Range("A" & .Rows.Count).End(xlUp).Row) = srcWS.Range("B6")
            End With
        Else
            lastRow2 = desWS.Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            desWS.ListObjects.Item("tblCosting").ListRows.Add
            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
            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
        End If
    End With
    
    desWS.ListObjects("tblCosting").Sort.SortFields.Clear
    desWS.ListObjects("tblCosting").Sort.SortFields. _
        Add Key:=desWS.Range("tblCosting[Date]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With desWS.ListObjects("tblCosting").Sort
        .header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    With Application
        .CutCopyMode = False
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Here is a link to the most recent file:
https://www.dropbox.com/s/z187wkbubugrdyb/quoting tool 6.2.xlsm?dl=0



Thanks,
Dave

 

[dpaton05]

I tried to step through it and it stops at the same part of code but says Application defined or object defined error.

 

[dpaton05]

I found the error but I don't know how to fix it. How do I reference from within vba a table column that has a heading with 2 lines? I pressed alt+enter to get the second line.

The Date in the above workbook field has 2 lines.

 

[dpaton05]

I found out how to do that problem. It was working good but then I showed my supervisor and he wants the date to be in the format, dd/mm/yyyy so I tried to change some things on the spreadsheet and I stuffed up the spreadsheet. It was copying 1 line of code to be 2 lines of code in the costing tool sheet just like before and I don't know how to get it copying back to one line of code but using the date format to be dd/mm/yyyy on both sheets.

I was also wondering, how do I find and get rid of the external link whenever you open my spreadsheet?

Here is the most recent file
https://www.dropbox.com/s/5gdfcl6651wy105/quoting tool 6.3.xlsm?dl=0


Thanks Mumps,
Dave

 

[mumps]

Click here for your file.

 

[dpaton05]

Thank you so much Mumps!!!

 

[dpaton05]

I would love to learn how you did a lot of the things for this spreadsheet, if it's not too much trouble, do you think you could put a comment on each line of code so I could understand what each line of code is doing and hopefully learn some vba please?