Transfer userform contents to Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an userform with 60 textboxes (12 lines of 5) that which contents, if
non blank , I would like to place into a sheet in the same order and then be
able to keep doing it starting at the next available row.
 
Not sure if you are using worksheet forms or VBA userfroms. Here is some
sample code that may help.

Sub saveuserform()

Const SaveSheet = "Summary"

With Sheets(SaveSheet)
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then
RowOff = 0
Else
RowOff = LastRow
End If

.Range("A1").Offset(RowOff, 0) = userform1.textbox1.Text
.Range("B1").Offset(RowOff, 0) = userform1.textbox2.Text
.Range("C1").Offset(RowOff, 0) = userform1.textbox3.Text
.Range("D1").Offset(RowOff, 0) = userform1.textbox4.Text
.Range("E1").Offset(RowOff, 0) = userform1.textbox5.Text
.Range("A2").Offset(RowOff, 0) = userform1.textbox6.Text
.Range("B2").Offset(RowOff, 0) = userform1.textbox7.Text
.Range("C2").Offset(RowOff, 0) = userform1.textbox8.Text
.Range("D2").Offset(RowOff, 0) = userform1.textbox9.Text
.Range("E2").Offset(RowOff, 0) = userform1.textbox10.Text
.Range("A3").Offset(RowOff, 0) = userform1.textbox11.Text
.Range("B3").Offset(RowOff, 0) = userform1.textbox12.Text
.Range("C3").Offset(RowOff, 0) = userform1.textbox13.Text
.Range("D3").Offset(RowOff, 0) = userform1.textbox14.Text
.Range("E3").Offset(RowOff, 0) = userform1.textbox15.Text
.Range("A4").Offset(RowOff, 0) = userform1.textbox16.Text
.Range("B4").Offset(RowOff, 0) = userform1.textbox17.Text
.Range("C4").Offset(RowOff, 0) = userform1.textbox18.Text
.Range("D4").Offset(RowOff, 0) = userform1.textbox19.Text
.Range("E4").Offset(RowOff, 0) = userform1.textbox20.Text
.Range("A5").Offset(RowOff, 0) = userform1.textbox21.Text
.Range("B5").Offset(RowOff, 0) = userform1.textbox22.Text
.Range("C5").Offset(RowOff, 0) = userform1.textbox23.Text
.Range("D5").Offset(RowOff, 0) = userform1.textbox24.Text
.Range("E5").Offset(RowOff, 0) = userform1.textbox25.Text
.Range("A6").Offset(RowOff, 0) = userform1.textbox26.Text
.Range("B6").Offset(RowOff, 0) = userform1.textbox27.Text
.Range("C6").Offset(RowOff, 0) = userform1.textbox28.Text
.Range("D6").Offset(RowOff, 0) = userform1.textbox29.Text
.Range("E6").Offset(RowOff, 0) = userform1.textbox30.Text
.Range("A7").Offset(RowOff, 0) = userform1.textbox31.Text
.Range("B7").Offset(RowOff, 0) = userform1.textbox32.Text
.Range("C7").Offset(RowOff, 0) = userform1.textbox33.Text
.Range("D7").Offset(RowOff, 0) = userform1.textbox34.Text
.Range("E7").Offset(RowOff, 0) = userform1.textbox35.Text
.Range("A8").Offset(RowOff, 0) = userform1.textbox36.Text
.Range("B8").Offset(RowOff, 0) = userform1.textbox37.Text
.Range("C8").Offset(RowOff, 0) = userform1.textbox38.Text
.Range("D8").Offset(RowOff, 0) = userform1.textbox39.Text
.Range("E8").Offset(RowOff, 0) = userform1.textbox40.Text
.Range("A9").Offset(RowOff, 0) = userform1.textbox41.Text
.Range("B9").Offset(RowOff, 0) = userform1.textbox42.Text
.Range("C9").Offset(RowOff, 0) = userform1.textbox43.Text
.Range("D9").Offset(RowOff, 0) = userform1.textbox44.Text
.Range("E9").Offset(RowOff, 0) = userform1.textbox45.Text
.Range("A10").Offset(RowOff, 0) = userform1.textbox46.Text
.Range("B10").Offset(RowOff, 0) = userform1.textbox47.Text
.Range("C10").Offset(RowOff, 0) = userform1.textbox48.Text
.Range("D10").Offset(RowOff, 0) = userform1.textbox49.Text
.Range("E10").Offset(RowOff, 0) = userform1.textbox50.Text
.Range("A11").Offset(RowOff, 0) = userform1.textbox51.Text
.Range("B11").Offset(RowOff, 0) = userform1.textbox52.Text
.Range("C11").Offset(RowOff, 0) = userform1.textbox53.Text
.Range("D11").Offset(RowOff, 0) = userform1.textbox54.Text
.Range("E11").Offset(RowOff, 0) = userform1.textbox55.Text
.Range("A12").Offset(RowOff, 0) = userform1.textbox56.Text
.Range("B12").Offset(RowOff, 0) = userform1.textbox57.Text
.Range("C12").Offset(RowOff, 0) = userform1.textbox58.Text
.Range("D12").Offset(RowOff, 0) = userform1.textbox59.Text
.Range("E12").Offset(RowOff, 0) = userform1.textbox60.Text
End With
End Sub

'another method if using worksheets

Sub saveuserform1()

Const SaveSheet = "Summary"

With Sheets(SaveSheet)
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then
RowOff = 0
Else
RowOff = LastRow
End If

Sheets("Old Sheets").Range("A1:E12").Copy _
Destination:=.Range("A1").Offset(RowOff, 0)
End With
End Sub
 
Works great. Thank you.

Joel said:
Not sure if you are using worksheet forms or VBA userfroms. Here is some
sample code that may help.

Sub saveuserform()

Const SaveSheet = "Summary"

With Sheets(SaveSheet)
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then
RowOff = 0
Else
RowOff = LastRow
End If

.Range("A1").Offset(RowOff, 0) = userform1.textbox1.Text
.Range("B1").Offset(RowOff, 0) = userform1.textbox2.Text
.Range("C1").Offset(RowOff, 0) = userform1.textbox3.Text
.Range("D1").Offset(RowOff, 0) = userform1.textbox4.Text
.Range("E1").Offset(RowOff, 0) = userform1.textbox5.Text
.Range("A2").Offset(RowOff, 0) = userform1.textbox6.Text
.Range("B2").Offset(RowOff, 0) = userform1.textbox7.Text
.Range("C2").Offset(RowOff, 0) = userform1.textbox8.Text
.Range("D2").Offset(RowOff, 0) = userform1.textbox9.Text
.Range("E2").Offset(RowOff, 0) = userform1.textbox10.Text
.Range("A3").Offset(RowOff, 0) = userform1.textbox11.Text
.Range("B3").Offset(RowOff, 0) = userform1.textbox12.Text
.Range("C3").Offset(RowOff, 0) = userform1.textbox13.Text
.Range("D3").Offset(RowOff, 0) = userform1.textbox14.Text
.Range("E3").Offset(RowOff, 0) = userform1.textbox15.Text
.Range("A4").Offset(RowOff, 0) = userform1.textbox16.Text
.Range("B4").Offset(RowOff, 0) = userform1.textbox17.Text
.Range("C4").Offset(RowOff, 0) = userform1.textbox18.Text
.Range("D4").Offset(RowOff, 0) = userform1.textbox19.Text
.Range("E4").Offset(RowOff, 0) = userform1.textbox20.Text
.Range("A5").Offset(RowOff, 0) = userform1.textbox21.Text
.Range("B5").Offset(RowOff, 0) = userform1.textbox22.Text
.Range("C5").Offset(RowOff, 0) = userform1.textbox23.Text
.Range("D5").Offset(RowOff, 0) = userform1.textbox24.Text
.Range("E5").Offset(RowOff, 0) = userform1.textbox25.Text
.Range("A6").Offset(RowOff, 0) = userform1.textbox26.Text
.Range("B6").Offset(RowOff, 0) = userform1.textbox27.Text
.Range("C6").Offset(RowOff, 0) = userform1.textbox28.Text
.Range("D6").Offset(RowOff, 0) = userform1.textbox29.Text
.Range("E6").Offset(RowOff, 0) = userform1.textbox30.Text
.Range("A7").Offset(RowOff, 0) = userform1.textbox31.Text
.Range("B7").Offset(RowOff, 0) = userform1.textbox32.Text
.Range("C7").Offset(RowOff, 0) = userform1.textbox33.Text
.Range("D7").Offset(RowOff, 0) = userform1.textbox34.Text
.Range("E7").Offset(RowOff, 0) = userform1.textbox35.Text
.Range("A8").Offset(RowOff, 0) = userform1.textbox36.Text
.Range("B8").Offset(RowOff, 0) = userform1.textbox37.Text
.Range("C8").Offset(RowOff, 0) = userform1.textbox38.Text
.Range("D8").Offset(RowOff, 0) = userform1.textbox39.Text
.Range("E8").Offset(RowOff, 0) = userform1.textbox40.Text
.Range("A9").Offset(RowOff, 0) = userform1.textbox41.Text
.Range("B9").Offset(RowOff, 0) = userform1.textbox42.Text
.Range("C9").Offset(RowOff, 0) = userform1.textbox43.Text
.Range("D9").Offset(RowOff, 0) = userform1.textbox44.Text
.Range("E9").Offset(RowOff, 0) = userform1.textbox45.Text
.Range("A10").Offset(RowOff, 0) = userform1.textbox46.Text
.Range("B10").Offset(RowOff, 0) = userform1.textbox47.Text
.Range("C10").Offset(RowOff, 0) = userform1.textbox48.Text
.Range("D10").Offset(RowOff, 0) = userform1.textbox49.Text
.Range("E10").Offset(RowOff, 0) = userform1.textbox50.Text
.Range("A11").Offset(RowOff, 0) = userform1.textbox51.Text
.Range("B11").Offset(RowOff, 0) = userform1.textbox52.Text
.Range("C11").Offset(RowOff, 0) = userform1.textbox53.Text
.Range("D11").Offset(RowOff, 0) = userform1.textbox54.Text
.Range("E11").Offset(RowOff, 0) = userform1.textbox55.Text
.Range("A12").Offset(RowOff, 0) = userform1.textbox56.Text
.Range("B12").Offset(RowOff, 0) = userform1.textbox57.Text
.Range("C12").Offset(RowOff, 0) = userform1.textbox58.Text
.Range("D12").Offset(RowOff, 0) = userform1.textbox59.Text
.Range("E12").Offset(RowOff, 0) = userform1.textbox60.Text
End With
End Sub

'another method if using worksheets

Sub saveuserform1()

Const SaveSheet = "Summary"

With Sheets(SaveSheet)
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
If (LastRow = 1) And IsEmpty(.Cells(1, "A")) Then
RowOff = 0
Else
RowOff = LastRow
End If

Sheets("Old Sheets").Range("A1:E12").Copy _
Destination:=.Range("A1").Offset(RowOff, 0)
End With
End Sub
Click to expand...
 
Back
Top