Transfer userform contents to Sheet

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.
 
G

Guest

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
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

userform macro to recognise non-blank cell 7
referencing Sheet from UserForm code 3
UserForm - pointing control source to new sheet - long delay 5
Getting From UserForm TextBoxes To Lastrow On Active Sheet 3
Data from sheet, through userform to text box on the sheet. 4
Userform textbox string to header causes unwanted double spacing 4
Autoload UserForm TextBoxes 4
Sheet change event not fired when showing a userform modal 2

Top