Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 Ken Puls; 2012-10-24 at 05:27 PM. Reason: Cleaned up code

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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 by pomatrix; 2012-10-25 at 09:14 AM.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •