Sheet code to copy & paste a new row of Info to another sheet

P

pomatrix

New member
Joined
Oct 24, 2012
Messages
3
Reaction score
0
Points
0
Good day all,

I have a userform that once completed uses a command button to send all data to a record sheet ("Form") into a single Row format, It is then assigned a unique number, however I also require this data, in single Row, to be sent from ("Form" - A1:01) to a second sheet ("SWMS" - A1:01), please see my code below & as always any assistance would be greatly appreciated, thank you all & have a great day - Marco


Code: 
Private Sub cmdProcess_Click()
    
Dim LastRow As Object
    Set LastRow = Sheets("Form").Range("a65536").End(xlUp)
    
    Sheets("Form").Unprotect Password:="000"
    LastRow.Offset(1, 0).Value = TextBox1.Text
    LastRow.Offset(1, 1).Value = TextBox2.Text
    LastRow.Offset(1, 2).Value = TextBox3.Text
    LastRow.Offset(1, 3).Value = TextBox4.Text
    LastRow.Offset(1, 4).Value = TextBox5.Text
    LastRow.Offset(1, 5).Value = TextBox6.Text
    LastRow.Offset(1, 6).Value = TextBox7.Text
    LastRow.Offset(1, 7).Value = TextBox8.Text
    LastRow.Offset(1, 8).Value = TextBox9.Text
    LastRow.Offset(1, 9).Value = TextBox10.Text
    LastRow.Offset(1, 10).Value = TextBox11.Text
    LastRow.Offset(1, 11).Value = ComboBox1.Text
    LastRow.Offset(1, 13).Value = TextBox12.Text
    LastRow.Offset(1, 14).Value = TextBox13.Text
    Sheets("Form").Protect Password:="000"
    
    MsgBox "SWMS Processed"
End Sub
 
Last edited by a moderator:
Try this:

Code: 
Private Sub cmdProcess_Click()
    
    Dim ary(0, 14) As Variant
    Sheets("Form").Unprotect Password:="000"
    With Me
        ary(0, 0).Value = .TextBox1.Text
        ary(0, 1).Value = .TextBox2.Text
        ary(0, 2).Value = .TextBox3.Text
        ary(0, 3).Value = .TextBox4.Text
        ary(0, 4).Value = .TextBox5.Text
        ary(0, 5).Value = .TextBox6.Text
        ary(0, 6).Value = .TextBox7.Text
        ary(0, 7).Value = .TextBox8.Text
        ary(0, 8).Value = .TextBox9.Text
        ary(0, 9).Value = .TextBox10.Text
        ary(0, 10).Value = .TextBox11.Text
        ary(0, 11).Value = .ComboBox1.Text
        ary(0, 13).Value = .TextBox12.Text
        ary(0, 14).Value = .TextBox13.Text
    End With
    Sheets("Form").Range("a65536").End(xlUp).Offset(1, 0).Resize(1, 15).Value = ary()
    Sheets("Form").Protect Password:="000"
    Sheets("SWMS").Range("a65536").End(xlUp).Offset(1, 0).Resize(1, 15).Value = ary()
    MsgBox "SWMS Processed"
End Sub
 
Hi Ken,

Tried your code but I keep getting a "Run-time error '424': Object required?

I've since found a code during a web search & used your code to tweak it & now working perfect, thanks for your reply Ken & have a great day. - Marco

Code: 
Private Sub cmdProcess_Click()
  Dim LastRow As Long


  LastRow = Sheets("Form").Range("a65536").End(xlUp).Row
  Sheets("Form").Unprotect Password:="000"
  Sheets("SWMS").Unprotect Password:="000"
  With Sheets("Form")
    .Range("A" & LastRow + 1).Value = TextBox1.Text
    .Range("B" & LastRow + 1).Value = TextBox2.Text
    .Range("C" & LastRow + 1).Value = TextBox3.Text
    .Range("D" & LastRow + 1).Value = TextBox4.Text
    .Range("E" & LastRow + 1).Value = TextBox5.Text
    .Range("F" & LastRow + 1).Value = TextBox6.Text
    .Range("G" & LastRow + 1).Value = TextBox7.Text
    .Range("H" & LastRow + 1).Value = TextBox8.Text
    .Range("I" & LastRow + 1).Value = TextBox9.Text
    .Range("J" & LastRow + 1).Value = TextBox10.Text
    .Range("K" & LastRow + 1).Value = TextBox11.Text
    .Range("L" & LastRow + 1).Value = ComboBox1.Text
    .Range("M" & LastRow + 1).Value = TextBox12.Text
    .Range("N" & LastRow + 1).Value = TextBox13.Text
  End With
  
LastRow = Sheets("Form").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Form").Range("A" & LastRow & ":O" & LastRow).Copy Worksheets("SWMS").Range("A1")
Sheets("Form").Protect Password:="000"
Sheets("SWMS").Protect Password:="000"


    MsgBox "SWMS Processed"


End Sub

How do I mark this thread as solved?
 
Last edited:
Ah, apologies. I gave code for a userform, not for textboxes on the worksheet, my bad. Glad you got it sorted though.

As far as marking it solved, you can edit the subject to preface it with solved, or just leave it as is. I don't have an easy function to mark them complete. (At least, not yet.)
 
You must log in or register to reply here.
Back
Top