+ Reply to Thread
Results 1 to 6 of 6

How to create a log of changes?

  1. #1
    Registered User
    Join Date
    01-17-2016
    Location
    Perth, Australia
    MS-Off Ver
    2010
    Posts
    3

    How to create a log of changes?

    Hi All,

    I have created a spreadsheet to track materials as to qty, location and other information needed.
    Sometimes these materials have to be used for one job, returned and sent to another site.

    What i'm chasing is a way to log in a separate sheet all the changes that have happened to each Drum# (Identifier) so that i know where they have been previously.

    E.g. - If A-001 needs to be sent to site, I will change LOC & BIN accordingly. If it was to return with less qty, i would then update the cells accordingly. I would need for the Log to show that it has gone to site, and came back from site with the different qty and LOC etc.

    Hopefully i have explained this well enough.
    Attached is a sample of the sheet i have created, I currently have almost 2000 Drum# on this sheet.
    I'm happy to use VBA with this, as i'm sure it will be required for such a thing.

    Regards,
    Bam
    DrumsEG.JPG

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: How to create a log of changes?

    try the file...

    log the data and it will update the latest entry in the masters
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    01-17-2016
    Location
    Perth, Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: How to create a log of changes?

    Hi rcm,

    Thanks for the help. I have opened the file and had a look.
    It is close to what I'm chasing, Is it possible for the log to automatically generate every time the master is changed?

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: How to create a log of changes?

    yes it is, then an automatic stamp for the date, time and user would be used. That is the reason I wrote the code to enter the changes in the log and the master would change. But you are the boss. I will work on that other version

  5. #5
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: How to create a log of changes?

    here you go mate
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-17-2016
    Location
    Perth, Australia
    MS-Off Ver
    2010
    Posts
    3

    Re: How to create a log of changes?

    Hey Mate,

    Thanks Heaps for that. You're a Star!
    I have since altered it slightly to watch a wider range of cells and spit out more information.
    This will definitely make my life a hell of a lot easier for tracking materials.

    Here is my updated version of your creation:

    Private Sub Worksheet_Change(ByVal Target As Range)

    'Create log of every change made in Log
    If Target.Column >= 1 And (Target.Column <= 10) Then
    rmax = Sheets("log").Range("A" & Rows.Count).End(xlUp).Row + 1
    With Sheets("log")
    .Cells(rmax, 1) = Now()
    .Cells(rmax, 2) = Environ("USERNAME")
    For i = 1 To 10
    .Cells(rmax, i + 2) = Sheets("DR # ").Cells(Target.Row, i)
    Next i
    End With
    End If
    End Sub

    Thanks again for your help

    Bam

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macros to Create New Sheet, Rename, and Create HyperLink to New Worksheet
    By jacksum in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2015, 12:44 PM
  2. Macro to find a word in a cell, create a transparent text zone over it, create a hyperlink
    By martin brandl in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-17-2015, 03:24 PM
  3. Replies: 1
    Last Post: 07-23-2014, 04:33 AM
  4. [SOLVED] Help me fix attached code (create Outlook email then create calendar item)
    By Rerock in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-19-2013, 08:15 PM
  5. Vba cmd1 create workbook cmd2 create worksheet depend upon combobox values
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2013, 03:20 AM
  6. Replies: 3
    Last Post: 11-12-2012, 10:33 AM
  7. Create Check List to Delete Columns and Create New File
    By margetc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2011, 05:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1