userform to 2 seperate sheets

  • Thread starter Thread starter ~Alan
  • Start date Start date
A

~Alan

XL2000 userform
I am using this code for a userform to add parts to a sheet named parts
and all works well as is
I would also like it to add just the value of this line
ws.Range("A" & r).Value = txtPartnumber.Value
to sheet2 column DR starting at DR2
is it possible?

Private Sub cmdAdd_Click()
Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
End Sub
 
with worksheets("sheet2")
.cells(.rows.count,"DR").end(xlup).offset(1,0).value _
= txtpartnumber.value
end with

will use the next available cell in column DR of sheet2.

(do it before you set txtpartnumber.value to "")
 
Private Sub cmdAdd_Click()

I tried it several different ways I even added in Set ws =
Worksheets("sheet2")
for some reason I cant get it too add the part number to DR it still
works in adding to sheet (parts)

Dim r As Long
Dim ws As Worksheet
Set ws = Worksheets("Parts")
Set ws = Worksheets("sheet2")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value
With Worksheets("sheet2")
.Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
= txtPartnumber.Value
End With
txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
End Sub
 
That ws variable can represent one worksheet at a time.

You could use two variables, though:

Dim r As Long
Dim ws1 As Worksheet
Set ws2 = Worksheets("Parts")
Set ws = Worksheets("sheet2")
With ws
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
End If
End With
r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Range("A" & r).Value = txtPartnumber.Value
ws.Range("B" & r).Value = txtQty.Value
ws.Range("C" & r).Value = txtdescription.Value
ws.Range("d" & r).Value = txtmanufacture.Value
ws.Range("E" & r).Value = txtprice.Value
ws.Range("F" & r).Value = txtunits.Value

With Ws2
.Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
= txtPartnumber.Value
End With

txtPartnumber.Value = ""
txtQty.Value = ""
txtdescription.Value = ""
txtmanufacture.Value = ""
txtprice.Value = ""
txtunits.Value = ""
txtParts.SetFocus
End Sub

(or you could have just dropped that second "Set ws = Worksheets("sheet2")" line
and used:

With Worksheets("sheet2")
.Cells(.Rows.Count, "DR").End(xlUp).Offset(1, 0).Value _
= txtPartnumber.Value
End With

I like your variable approach, though. Then if the worksheet names change, you
only have to fix it one spot.
 
Back
Top