Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Append new row of select data from one sheet to another

    I suspect this needs a macro.

    I have a template sheet (called "Dispatch"). A user enters data in various cells.
    I'd like a button that takes specific cells from the template sheet and places them in the next available row of another sheet (ultimately for further analysis).

    Suppose I want cells A2, B6, B8, A15 from the template to become a new (next available) row in "Collect" sheet in columns A : D.

    Thanks in advance.

    OK, I almost have it. What am I doing incorrectly here because the data, when I click the button on the Dispatch sheet always goes into row 2 on the target sheet (first row is header).

    Private Sub CommandButton1_Click()
    Dim LR As Long, i As Long, cls
    cls = Array("C2", "C3", "G4", "C12", "C35", "E15")
    With Sheets("Collect")
    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1
    For i = LBound(cls) To UBound(cls)
    .Cells(LR, i + 1).Value = Me.Range(cls(i)).Value
    Next i
    End With
    End Sub

    This seems to work, but I'm not sure why the earlier version didn't.

    Private Sub CommandButton1_Click()
    Dim LR As Long, i As Long, cls
    cls = Array("C2", "C3", "G4", "C12", "C35", "E15")
    With Sheets("Collect")
    LR = Worksheets("Collect").Cells(Rows.Count, 1).End(xlUp).Row + 1
    For i = LBound(cls) To UBound(cls)
    .Cells(LR, i + 1).Value = Me.Range(cls(i)).Value
    Next i
    End With
    End Sub
    Last edited by kweaver; 2014-09-25 at 19:14.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    KW,

    If you are running this with the Dispatch sheet active then remove the Activesheet in the line:

    LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1

    to look like

    LR = .Range("A" & Rows.Count).End(xlUp).Row + 1

    You are reading the last line on the active sheet instead of letting the "With" statement do its job"

    HTH,
    Maud
    Last edited by Maudibe; 2014-09-25 at 20:04.

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    or using cells
    lr=cells(rows.count,1).end(xlup).row+1

    or special cells last row on sheet

    lr = Cells.SpecialCells(xlCellTypeLastCell).Row

Posting Permissions

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