Object variable or with block variable not set

[dpaton05]

Hi,

Why am I getting the error of Object variable or with block variable not set when I try and run my code?

Sub cmdNot_Accept_Click()
Dim wsDst As Worksheet
Dim wsSrc As Worksheet
Dim tblrow As ListRow
Dim Combo As String
Dim sht As Worksheet
Dim tbl As ListObject
Dim LastRow As Long
Dim DocYearName As String
        
ActiveCell.EntireRow.Select
'With Selection
    If Intersect(ActiveCell.EntireRow, ActiveSheet.ListObjects("tblCosting").DataBodyRange) Is Nothing Then
          MsgBox "active row is NOT within the table"
       Else
          
          
           Combo = "Not accepted quotes"
                'lastrow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
                    
                If tblrow.Range.Cells(1, 6).Value = "Ang Wes" Then
                    DocYearName = tblrow.Range.Cells(1, 37).Value
                Else
                    DocYearName = tblrow.Range.Cells(1, 36).Value
                End If
                Set wsDst = Workbooks(DocYearName).Worksheets(Combo)
                    With wsDst
                        'This copies the first 10 columns, i.e. A:J, of the current row of the table to column A in the destination sheet.
                        tblrow.Range.Resize(, 8).Copy
                        .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
    
                            Rows("3:1000").Select
                            Workbooks(DocYearName).Worksheets(Combo).Sort.SortFields.Clear
                            Workbooks(DocYearName).Worksheets(Combo).Sort.SortFields.Add Key:=Range("A4:A1000"), _
                                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                                    With Workbooks(DocYearName).Worksheets(Combo).Sort
                                        .SetRange Range("A3:AJ1000")
                                        .header = xlYes
                                        .MatchCase = False
                                        .Orientation = xlTopToBottom
                                        .SortMethod = xlPinYin
                                        .Apply
                                    End With
                    End With
          
          
          
    End If
'End With
End Sub

The following line is highlighted:

If tblrow.Range.Cells(1, 6).Value = "Ang Wes" Then

 

[dpaton05]

I just want to allow a row to be selected and press a button and have the row moved to a not accepted quotes sheet of a workbook that the code is correctly identifying and copying to. I don't know how to code so I just tried to copy that code and modify it. Here is the original code that correctly identifies the workbook and sheet to put it in. The unaccepted quotes will go in the appropriate workbook but will go a Not Accepted Quotes sheet.

Sub cmdCopy()
        Dim wsDst As Worksheet
        Dim wsSrc As Worksheet
        Dim tblrow As ListRow
        Dim Combo As String
        Dim sht As Worksheet
        Dim tbl As ListObject
        Dim LastRow As Long
        Dim DocYearName As String
        
        Application.ScreenUpdating = False
        'assign values to variables
        
        Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
        For Each tblrow In tbl.ListRows
            If tblrow.Range.Cells(1, 1).Value = "" Or tblrow.Range.Cells(1, 5).Value = "" Or tblrow.Range.Cells(1, 6).Value = "" Then
                MsgBox "The Date, Service or Requesting Organisation has not been entered for every record in the table"
                Exit Sub
            End If
        Next tblrow
            
        For Each tblrow In tbl.ListRows
            Combo = tblrow.Range.Cells(1, 26).Value
            'lastrow = Worksheets(Combo).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
                
            If tblrow.Range.Cells(1, 6).Value = "Ang Wes" Then
                DocYearName = tblrow.Range.Cells(1, 37).Value
            Else
                DocYearName = tblrow.Range.Cells(1, 36).Value
            End If
            Set wsDst = Workbooks(DocYearName).Worksheets(Combo)
                With wsDst
                    'This copies the first 10 columns, i.e. A:J, of the current row of the table to column A in the destination sheet.
                    tblrow.Range.Resize(, 8).Copy
                    .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
                    
                    
                    
                    'tblrow.Range(Cells(1, 9), Cells(1, 10)).Copy
                    '.Cells.Range(1, 9).PasteSpecial xlPasteFormulas
                    
                    
'.Range("A" & Rows.Count).End(xlUp).Offset(8).PasteSpecial xlPasteFormulas
                    
                    'This should go to the 15th column in the current row, i.e. column O, and copy that column and the next 2 columns, i.e. O:Q, to column K on the destination sheet.
                        'tblrow.Range.Offset(, 14).Resize(, 3).copy
                        '.Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                    'Similarly this should copy columns AD:AF from the table to column N on the destination sheet.
                        'tblrow.Range.Offset(, 29).Resize(, 3).copy
                        '.Range("N" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
                    'Sort rows based on date
                        Rows("3:1000").Select
                        Workbooks(DocYearName).Worksheets(Combo).Sort.SortFields.Clear
                        Workbooks(DocYearName).Worksheets(Combo).Sort.SortFields.Add Key:=Range("A4:A1000"), _
                            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                                With Workbooks(DocYearName).Worksheets(Combo).Sort
                                    .SetRange Range("A3:AJ1000")
                                    .header = xlYes
                                    .MatchCase = False
                                    .Orientation = xlTopToBottom
                                    .SortMethod = xlPinYin
                                    .Apply
                                End With
                End With
        Next tblrow
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I'm never good at reading code written by someone else when I do not know what the user is trying to achieve.
Not sure if you wrote this code or found it some place and hope it can do what you want.

I see a lot of people on this forum say:
I found this code on the internet but it will not do what I want can you fix it so it will do what I want.
And not even explain what their trying to do.
I like users who say here is what I'm trying to do will you see if your can provide some code that will do what I want.

But then that is just me. There are others on this forum who are able to read code sort out what it's trying to do and modify it.

 

[My Aswer Is This]

Now I see your most recent post where you said this:

I just want to allow a row to be selected and press a button and have the row moved to a not accepted quotes sheet of a workbook that the code is correctly identifying and copying to.

So you want to:
1. Select a Row
2. Move To a sheet named What?
And what does move mean? Does it mean Copy to and delete from original sheet?

3. To a Workbook named what?
4. And to do this both Workbooks must be open


Please do not say read the code to answer these questions.

I see no reason why it would take all this much code to copy a selected row to another sheet on another Workbook

We would need both Sheet names
And both Workbook names

​

 

[My Aswer Is This]

To just copy the active row to another Workbook you would only need a code that looks like this:

See:
You have to provide both Workbook names and both sheet names.

Sub Copy_Row()
'Modified  4/13/2019  2:33:24 AM  EDT
Dim ans As String
ans = ThisWorkbook.Name
Workbooks(ans).Sheets("Me").Rows(ActiveCell.Row).Copy Workbooks("Highlight column.xlsm").Sheets(3).Rows(3)
End Sub

 

[dpaton05]

Thanks for getting back to me. Sorry for being a bit vague on how it worked, I will try and explain it.

I am making a spreadsheet for doing and recording quotes. I have a Sheet called NPSS_quote_sheet which allows me to compile a quote for services. There is a table in it called NPSS_quote and it can have x number of rows. Different parametres are entered and it uses index and match, match and other formulas to determine prices from tables in the background. When the quote is finished and my supervisor is happy with it, he presses a button that copies every row to a table called tblCosting in a sheet called Costing_tool. Here he is able to enter more information regarding each row in the quote. The quote is then either accepted or rejected. If it is accepted, you press copy to relevant sheet and keep contents and the code correctly moves each row in the table to multiple sheets called allocation sheets. The allocation sheets are financial year documents, such as 2018-2019 xxx. The code will move each row to the correct sheet based of the date, if it is in that financial year.

The piece that I needed help with is the unaccepted quotes button in the Costing_tool sheet. If a quote is not accepted, I want to highlight the row and press Unaccepted quotes, and have it cut to the unaccepted quotes sheet of the relevant yearly document. There are 2 types of allocation sheets for every year. One is "2018 - 2019 NPSS Work Allocation Sheet" and the other is "2018 - 2019 Internal Work Allocation Sheet. The name of the document is correctly identified in columns 36 or 37, depending on whether the requesting organisation is "Ang Wes" or not. With the unaccepted quotes section, it needs to paste them below the other rows in the table. I have uploaded a quote sheet and several allocation sheets for you to have a look at what is going on. Hopefully, it will help you debug it for me too.

Thanks,
Dave

https://www.dropbox.com/s/880db4q9tsi8unf/quoting tool 8.8 WCI.xlsm?dl=0

https://www.dropbox.com/s/9ur2snvnm78kj40/2018 - 2019 Internal Work Allocation Sheet.xlsm?dl=0

https://www.dropbox.com/s/888j84blv82a09v/2018 - 2019 NPSS Work Allocation Sheet.xlsm?dl=0

 

[Michael M]

I think I've mentioned this before.....

tblrow.Range.Cells(1, 6).Value

tblrow of what table ??

AND

Range.Cells(1, 6)

Range OR Cells ??

 

[dpaton05]

I think I've mentioned this before.....

tblrow.Range.Cells(1, 6).Value

tblrow of what table ??

AND

Range.Cells(1, 6)

Range OR Cells ??

Thanks Michael but my supervisor doesn't want that feature any more.

 

[Michael M]

What feature ???

 

[dpaton05]

He doesn't want the ability to seperate the unaccepted quotes to a seperate sheet anymore, he just wants to keep them on the same sheet as the accepted quotes.