Userform data overwrites 1st table row

[Gowapiti]

Hello, This is my first post here and I have to say thank you already to many of you for helping me get as far as I have with my current project as I have found many answers by scouring these threads. However, an answer to my current problem is eluding me after hours of searching.

I have a userform with 10 data fields. I am transferring these data fields to two separate tables, one table in each of two sheets. The sheets are named "Quotation" and "Internal Use Only" and as indicated by the code below I am placing 7 fields on one sheet and 4 fields on another sheet. I have each field going to the correct sheet when I add the first item via the userform. When I add the second item and click NEXT ITEM or CLOSE ITEMS on my userform, the new item overwrites the previously added item on row 1 of the tables.

This is the code that currently places my userform data in the correct table cells but always in row 1. Also when the ListRows.Add runs, it adds 2 new rows each time the sub is run.

Sub TransferDataToTable()


With Sheets("Quotation")


Worksheets("Quotation").Unprotect
        
        'Transfer Information to QUOTATION
        Dim ws1 As Worksheet
        Set ws1 = Sheets("Quotation")


    With ws1.ListObjects("Table1")
       ' Dim lastrow As Long
        Dim nr As ListRow
        ws1.Range("Table1").ListObject.ListRows.Add AlwaysInsert:=True
        Set nr = ws1.Range("Table1").ListObject.ListRows.Add(AlwaysInsert:=True)
       ' lastrow = ws1.ListObjects("Table1").Range.Rows.Count


    'Transfer Information to SHEET "QUOTATION" following this formulat format oNewRow.Range.Cells(1,1).Value="Value For New cell"
    
    ws1.Range("Table1").Cells(1, 2).Value = NewOrderUserForm.LOCATION.Value ' I have tried changing the .Cells(1 , 2) to .cells(lastrow+1) and to .cells(nr ,2) as well as .cells(nr+1,2) and get a Type mismatch error
    ws1.Range("Table1").Cells(1, 3).Value = NewOrderUserForm.ROOMAREA.Value
    ws1.Range("Table1").Cells(1, 4).Value = NewOrderUserForm.QB_ITEMS.Value
    ws1.Range("Table1").Cells(1, 5).Value = NewOrderUserForm.VENDOR.Value
    ws1.Range("Table1").Cells(1, 6).Value = NewOrderUserForm.WOOD.Value
    ws1.Range("Table1").Cells(1, 7).Value = NewOrderUserForm.STAIN.Value
    ws1.Range("Table1").Cells(1, 8).Value = NewOrderUserForm.DOORPARTNUM.Value
    
    
    End With
    
End With




With Sheets("Internal Use Only")


   ' Dim lastrow2 As Long
    Dim ws2 As Worksheet
    Set ws2 = Sheets("Internal Use Only")


ws2.Unprotect
    
    'lastrow2 = ws2.ListObjects("Table2").Range.Rows.Count '(commented out as I got an error when using this statement
    ws2.Range("Table2").ListObject.ListRows.Add AlwaysInsert:=True
   Set nr = ws2.Range("table2").ListObject.ListRows.Add(AlwaysInsert:=True)
    
        'Transfer Information to sheet "Internal Use Only" following this formulat format oNewRow.Range.Cells(1,1).Value="Value For New cell"
    ws2.Range("Table2").Cells(1, 2).Value = NewOrderUserForm.QB_ITEMS.Value
    ws2.Range("Table2").Cells(1, 3).Value = NewOrderUserForm.VENDOR.Value
    ws2.Range("Table2").Cells(1, 5).Value = NewOrderUserForm.RETAILPRICE.Value
    ws2.Range("Table2").Cells(1, 14).Value = NewOrderUserForm.MARGIN.Value
    
   End With
   
   
ws1.Protect
ws2.Protect


End Sub

How do I get my code to add the next item to the next table row and not overwrite existing rows?

This sub is used both for the Next Item and Close Items cmdButtons on the userform both of which function correctly with the exception of overwriting the data in the first row of the corresponding table.

OS Win 10 64 bit
Excel 2010

I appreciate any help

 

[RoryA]

You have two Listrows.add lines which is why you get two new rows each time. Remove the first one. Then you can use the nr variable you created:

        Set nr = ws1.Range("Table1").ListObject.ListRows.Add(AlwaysInsert:=True)
    
    nr.Range(1, 2).Value = NewOrderUserForm.LOCATION.Value ' I have tried changing the .Cells(1 , 2) to .cells(lastrow+1) and to .cells(nr ,2) as well as .cells(nr+1,2) and get a Type mismatch error
    nr.Range(1, 3).Value = NewOrderUserForm.ROOMAREA.Value
    nr.Range(1, 4).Value = NewOrderUserForm.QB_ITEMS.Value
    nr.Range(1, 5).Value = NewOrderUserForm.VENDOR.Value
    nr.Range(1, 6).Value = NewOrderUserForm.WOOD.Value
    nr.Range(1, 7).Value = NewOrderUserForm.STAIN.Value
    nr.Range(1, 8).Value = NewOrderUserForm.DOORPARTNUM.Value

 

[Gowapiti]

Thank you Rory! That solution worked great. I forgot one element in my new row and that is whether the item is taxable or not. i have two option buttons on my userform Yes|No to determine taxable status. When I add the code below in red to account for the tax status the tax calculates correctly, but i get and "End With without With Block" error. if I comment out the IF THEN ELSE to set tax status, the sub runs and I do not get the "End With without With Block" error but i do not get a value for the tax status. Thanks.

Sub TransferDataToTable()


With Sheets("Quotation")


Worksheets("Quotation").Unprotect






        'Transfer Information to QUOTATION
        
        Dim ws1 As Worksheet
        Set ws1 = Sheets("Quotation")
             
            With ws1.ListObjects("Table1")
            
                Dim nr As ListRow
                Set nr = ws1.Range("Table1").ListObject.ListRows.Add(AlwaysInsert:=True)
                      
            'Transfer Information to SHEET "QUOTATION" oNewRow.Range.Cells(1,1).Value="Value For New cell"
            
            nr.Range(1, 2).Value = NewOrderUserForm.LOCATION.Value
            nr.Range(1, 3).Value = NewOrderUserForm.ROOMAREA.Value
            nr.Range(1, 4).Value = NewOrderUserForm.QB_ITEMS.Value
            nr.Range(1, 5).Value = NewOrderUserForm.VENDOR.Value
            nr.Range(1, 6).Value = NewOrderUserForm.WOOD.Value
            nr.Range(1, 7).Value = NewOrderUserForm.STAIN.Value
            nr.Range(1, 8).Value = NewOrderUserForm.DOORPARTNUM.Value
            'use the following IF THEN ELSE statement to set tax status to Yes or No
             'If NewOrderUserForm.OptionButton3 = True Then
             '                   nr.Range(1, 9).Value = NewOrderUserForm.OptionButton3.Caption
             '              Else
             '               If NewOrderUserForm.OptionButton4 = True Then
             '                   nr.Range(1, 9).Value = NewOrderUserForm.OptionButton4.Caption
             '           End If
            
            End With ' "End With without With Block" error if the code in red is not commented out, with code commented out everything works just fine.


End With ' "End With without With Block" duplicates to this End With line also.  If I remove both End With statements and click "Next Item" only the last QB_ITEM with its related elements from the                            UserForm  is transferred to the Internal Use Only worksheet






With Sheets("Internal Use Only")


   ' Dim lastrow2 As Long
    Dim ws2 As Worksheet
    Set ws2 = Sheets("Internal Use Only")


ws2.Unprotect
    
    'lastrow2 = ws2.ListObjects("Table2").Range.Rows.Count
    ws2.Range("Table2").ListObject.ListRows.Add AlwaysInsert:=True
   Set nr = ws2.Range("table2").ListObject.ListRows.Add(AlwaysInsert:=True)
    
        'Transfer Information to sheet "Internal Use Only"
    nr.Range(1, 2).Value = NewOrderUserForm.QB_ITEMS.Value
    nr.Range(1, 3).Value = NewOrderUserForm.VENDOR.Value
    nr.Range(1, 5).Value = NewOrderUserForm.RETAILPRICE.Value
    nr.Range(1, 14).Value = NewOrderUserForm.MARGIN.Value
   
    
        'set NONTAXABLE value
        'If NewOrderUserForm.NONTAXABLE.Value = True Then
           ' rng.Parent.Cells(lastrow + 1, 8).Value = "No"
        'Else
        '    .rng.Parent.Cells(lastrow + 1, 8).Value = "Yes"
       ' End If
      
   End With
   
   
ws1.Protect
ws2.Protect


End Sub

I know the problem is the code I am using to set the tax status but not sure how to fix it. Thanks for any help offered.

 

[RoryA]

You were missing an End If, but you can also use an ElseIf clause like this:

             If NewOrderUserForm.OptionButton3 = True Then
                                nr.Range(1, 9).Value = NewOrderUserForm.OptionButton3.Caption
                           ElseIf NewOrderUserForm.OptionButton4 = True Then
                                nr.Range(1, 9).Value = NewOrderUserForm.OptionButton4.Caption
                        End If

 

[Gowapiti]

Thanks Rory, I thought I had tied out all of the End If and End With to their appropriate statements. Guess my eyes are tired of looking at the screen.

I appreciate the help!

 

[RoryA]

Glad to help.