Update Tables from Multi page data VBA

[karolina1406]

I am using MultiPage in my Userform to update 2 separate Excel tables. I have below code to update first table called "ReservCards " from page 1 called "Cards" and it workds perfectly. How can i improve the code to update my second table called "ReservCables", with the same data but from page 2 called "cables" and my textBoxes are called "cablestype" and "cablequantity"

Dim oNewRow As ListRow
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Available_Stock").Range("ReservCards")
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
With ws
    oNewRow.Range.Cells(1, 2).Value = Date
    oNewRow.Range.Cells(1, 3).Value = Me.cardtype.Value
    oNewRow.Range.Cells(1, 4).Value = Me.cardquantity.Value

 

[DanteAmor]

Just repeat the code.
It doesn't matter which page the textboxes are on, they are unique.

Private Sub CommandButton1_Click()
  Dim oNewRow As ListRow
  Dim rng As Range
  
  Set rng = ThisWorkbook.Worksheets("Available_Stock").Range("ReservCards")
  Set oNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)
  oNewRow.Range.Cells(1, 2).Value = Date
  oNewRow.Range.Cells(1, 3).Value = Me.cardtype.Value
  oNewRow.Range.Cells(1, 4).Value = Me.cardquantity.Value

  Set rng = ThisWorkbook.Worksheets("Available_Stock").Range("ReservCables")
  Set oNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)
  oNewRow.Range.Cells(1, 2).Value = Date
  oNewRow.Range.Cells(1, 3).Value = Me.cablestype.Value
  oNewRow.Range.Cells(1, 4).Value = Me.cablequantity.Value
End Sub

 

[DanteAmor]

In fact if it's in the same book, it could be like this:

Private Sub CommandButton1_Click()
  With Range("ReservCards").ListObject.ListRows.Add(AlwaysInsert:=True).Range
    .Cells(1, 2).Value = Date
    .Cells(1, 3).Value = Me.cardtype.Value
    .Cells(1, 4).Value = Me.cardquantity.Value
  End With
  
  With Range("ReservCables").ListObject.ListRows.Add(AlwaysInsert:=True).Range
    .Cells(1, 2).Value = Date
    .Cells(1, 3).Value = Me.cablestype.Value
    .Cells(1, 4).Value = Me.cablequantity.Value
  End With
End Sub

 

[karolina1406]

Just repeat the code.
It doesn't matter which page the textboxes are on, they are unique.

Private Sub CommandButton1_Click()
  Dim oNewRow As ListRow
  Dim rng As Range
 
  Set rng = ThisWorkbook.Worksheets("Available_Stock").Range("ReservCards")
  Set oNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)
  oNewRow.Range.Cells(1, 2).Value = Date
  oNewRow.Range.Cells(1, 3).Value = Me.cardtype.Value
  oNewRow.Range.Cells(1, 4).Value = Me.cardquantity.Value

  Set rng = ThisWorkbook.Worksheets("Available_Stock").Range("ReservCables")
  Set oNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)
  oNewRow.Range.Cells(1, 2).Value = Date
  oNewRow.Range.Cells(1, 3).Value = Me.cablestype.Value
  oNewRow.Range.Cells(1, 4).Value = Me.cablequantity.Value
End Sub

hi,
I just tried to apply this code to a different task and the difference is, that a CommanButton 1 is in a Tab called "Requests" and i have an error at line Set rng = ThisWorkbook.Worksheets("Available_Stock").Range("ReservCards")

any idea why?

 

[DanteAmor]

What does the error message say?
You must change the name of the sheet to " Requests " and also in that sheet you must have a named range, you must update the named range in the code line.

ThisWorkbook.Worksheets("Requests").Range("?????")

 

[karolina1406]

What does the error message say?
You must change the name of the sheet to " Requests " and also in that sheet you must have a named range, you must update the named range in the code line.

ThisWorkbook.Worksheets("Requests").Range("?????")

ok, got it but the next problem occurred
I use now below code to update 2 different tables in 2 different tabs. It works, however, want to improve it to reflect that if CBCode2 box is empty - then ReservedOptics table will not be populated while ReservedCards table will be (because CbCode will not be empty)... is it doable?

Dim oNewRow As ListRow
Dim rng As Range
  
Set rng = ThisWorkbook.Worksheets("cards").Range("ReservedCards")
Set oNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)
oNewRow.Range.Cells(1, 2).Value = "Reserved"
    oNewRow.Range.Cells(1, 3).Value = Date
    oNewRow.Range.Cells(1, 4).Value = Me.Requestor.Value
    oNewRow.Range.Cells(1, 5).Value = Me.email.Value
    oNewRow.Range.Cells(1, 6).Value = Me.ProjectID.Value
    oNewRow.Range.Cells(1, 7).Value = Me.CostCentre.Value
    oNewRow.Range.Cells(1, 8).Value = Me.PO.Value
    oNewRow.Range.Cells(1, 9).Value = Me.DTPicker1.Value
    oNewRow.Range.Cells(1, 10).Value = Me.CBCode.Value
    oNewRow.Range.Cells(1, 11).Value = Me.RequestedQuantity.Value

Set rng = ThisWorkbook.Worksheets("optics").Range("Reservedoptics")
Set oNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)
oNewRow.Range.Cells(1, 3).Value = Date
oNewRow.Range.Cells(1, 4).Value = Me.Requestor.Value
oNewRow.Range.Cells(1, 5).Value = Me.email.Value
oNewRow.Range.Cells(1, 6).Value = Me.ProjectID.Value
oNewRow.Range.Cells(1, 7).Value = Me.CostCentre.Value
oNewRow.Range.Cells(1, 8).Value = Me.PO.Value
oNewRow.Range.Cells(1, 9).Value = Me.DTPicker1.Value
oNewRow.Range.Cells(1, 10).Value = Me.CBCode2.Value
oNewRow.Range.Cells(1, 11).Value = Me.RequestedQuantity2.Value