Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Adding to a data list

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to be able to add data from one list onto the bottom of another list.ie adding data gerenrated in a program to a history list. Does anyone have any ideas ?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Copy/Paste

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This will not work because i want it to do this automaticaly and constantly update a data file, if i use a copy paste function it will not add to the list it will overwrite the previous data, each time i add to the history file. A macro recorded with copy paste just over wites the previous data.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not if you paste below the previous data.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i need to have the process automated so i want it to add to the end of the history file which will vary in lenght i want it to select the right paste area, ie apending to the end of the history data sheet each time the program is run.

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ontario, Canada
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I know there are other ways, but this should work you'll have to play around with the cell reference.

    Use a named cell reference in to indicate the end of your sheet. Use that reference in the Macro, then offset and insert the copied cells. do it so the named reference gets pushed to the bottom every time.

    This sort of works (below) I didn't have time to get it to work properly.
    It opens a file copies a range closes the book then opens the destination book selects the named reference and pastes (thats the part you'll have to play around with)

    Workbooks.Open Filename:="C:My DocumentsKNROW COUNT.XLS"
    Range("A1651:A1665").Select
    Selection.Copy
    ActiveWindow.Close
    Workbooks.Open Filename:="C:My DocumentsKNcopypastetobottom.xls"
    Range("blankline").Select
    ''ActiveCell.Offset(-1, 0).Range _("blankline").Select
    Selection.EntireRow.Insert
    ActiveSheet.Paste
    Selection.Insert Shift:=xlDown


    Hope it helps

    Ziggy



  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    This is a post I just gave to another question but it might give you some ideas for your own problem.

    I'm afraid I am a self-taught macro man so my code would probably be laughed at by proper programmers. Anyhow If I wanted to keep copying rows 1:10 from Book5 to Book6 this is the code I would use.
    This goes to the receiving Book6 first to select the cell where you are going to dump, then returns to Book5 to copy the rows needed
    The first dump is on line 2 of Book6, thereafter it dumps on the next free row.


    Application.ScreenUpdating = False
    Windows("Book6").Activate
    Count = 0
    For Each cell In [A65536:IV65536]
    If cell.End(xlUp).Row > Count Then
    Count = cell.End(xlUp).Row
    End If
    Next cell
    Range("A" & Count + 1).Select
    Windows("Book5").Activate
    Rows("1:10").Select
    Selection.Copy
    Windows("Book6").Activate
    ActiveSheet.Paste
    Windows("Book5").Activate
    Application.CutCopyMode = False
    End Sub

    Hope this is of some help
    Regards
    Derek

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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