CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    16

    Copying data from one cell to another in Excel

    I am trying to write a Macro in Excel which makes a copy of some of the information entered and a timestamp of when the change was made. It also creates a new entry if existing information is edited and it gives the timestamp.


    So far I have the following code:


    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim AuditRecord As Range

    ' This is our change history

    Set AuditRecord = Worksheets("ChangeHistory").Range("A1:B1")

    r = 0

    'Now find the end of the Change History to start appending to ...

    Do

    r = r + 1

    Loop Until IsEmpty(AuditRecord.Cells(r, 2))

    'For each cell modified ...

    For Each c In Target

    Value = c.Value

    Row = c.Row

    ' ...update Change History with Value and time stamp of modification

    AuditRecord.Cells(r, 1) = Worksheets("Products").Cells(Row, 1)

    AuditRecord.Cells(r, 2) = Worksheets("Products").Cells(Row, 2)

    AuditRecord.Cells(r, 3).NumberFormat = "hh:mm:ss"

    AuditRecord.Cells(r, 3).Value = Now

    Next



    This copies the data from the "Products" sheet into the "ChangeHistory" sheet and applies the timestamp. However I need the data copied into the same sheet (Products) ,and starting from row 200.

    I have tried many different things but cannot get this to work on the same sheet.

    Is this even achievable?

  2. #2
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Copying data from one cell to another in Excel

    If you can do it in a spreadsheet, you can RECORD A MACRO and then LOOK at the code. Nobody writes macros....
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: Copying data from one cell to another in Excel

    The problem with writing the change log to the same sheet probably is that writing to the change log cells re-triggers the worksheet change event, tossing your code into an infinite recursion. Try wrapping the entire body of your Worksheet_Change() handler into an If condition that skips it when it detects the write goes to the change log. (Alternatively, do an Exit Sub right at the start of the handler upon detection of a log write.)

    Quote Originally Posted by dglienna View Post
    Nobody writes macros....
    I do.
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

Tags for this Thread

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured