Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    How can I copy a row of data (it will always be the first row) from one worksheet/workbook to a worksheet in another workbook and add it below and ever increasing list of data?

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This will copy the first row on the current sheet and paste the data in the first open row on Sheet2

    Code:
    Cells(1, 1).EntireRow.Copy Destination:=Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)

  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='796761' date='07-Oct-2009 13:53']This will copy the first row on the current sheet and paste the data in the first open row on Sheet2

    Code:
    Cells(1, 1).EntireRow.Copy Destination:=Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    [/quote]
    HI,

    Thanks for the speedy reply.

    I must have been unclear...I was wanting to take a line from a new worksheet/workbook and add it to an existing named worksheet/workbook under previously added data.

    I would be doing this on a regular basis, exact process currently is:

    Export data from a web page to an excel file

    The data is always a single row in a single worksheet in the newly created workbook

    I then want to add this data to an existing worksheet in an established workbook, below the last used line of data.
    I am doing this currently by copying and pasting but I hope that there is a smarter way!

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='trulyfayre' post='796769' date='07-Oct-2009 14:31']HI,

    Thanks for the speedy reply.

    I must have been unclear...I was wanting to take a line from a new worksheet/workbook and add it to an existing named worksheet/workbook under previously added data.

    I would be doing this on a regular basis, exact process currently is:

    Export data from a web page to an excel file

    The data is always a single row in a single worksheet in the newly created workbook

    I then want to add this data to an existing worksheet in an established workbook, below the last used line of data.
    I am doing this currently by copying and pasting but I hope that there is a smarter way![/quote]
    Additional: The single line to be exported is row 2, row 1 contains consistent headers, the same headers are used in the sheet I want to add this data to.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    Dim wbkTo As Workbook
    Set wbkTo = Workbooks("projects.xls")
    Cells(2, 1).EntireRow.Copy Destination:=wbkTo.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Change projects.xls to the correct name of your workbook
    Requires the destination book to be open.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Mike Barron's code can easily be adapted to copy the data to a sheet in another workbook (which has to be opened in the same instance of Excel).

    Let's say that the workbook is named MyData.xls and that the worksheet is named Data.

    You could create a macro in this workbook or in your personal macro workbook Personal.xls. You could assign it to a custom toolbar button (or to the Quick Access Toolbar if you have Excel 2007).

    Code:
    Sub CopyData()
      Dim wsh As Worksheet
      Set wsh = Workbooks("MyData").Worksheets("Data")
      ActiveSheet.Cells(1, 1).EntireRow.Copy Destination:=wsh.Cells(wsh.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End Sub
    This macro should be run while the new workbook (with just one row of data) is the active workbook.

  7. #7
    Lounger
    Join Date
    Oct 2001
    Location
    Erskine, Renfrewshire, Scotland
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='796772' date='07-Oct-2009 15:01']
    Code:
    Dim wbkTo As Workbook
    Set wbkTo = Workbooks("projects.xls")
    Cells(2, 1).EntireRow.Copy Destination:=wbkTo.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Change projects.xls to the correct name of your workbook
    Requires the destination book to be open.[/quote]
    Thanks very much for your help!

Posting Permissions

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