| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Track changes by row in different worksheet - History tracking
I am looking for code that would copy a row that a change is being made on to
a separate worksheet. The data being copied would be the data before the change. I have found code that create tracking base on each change made to each cell...but I would like to have it base on the entire row of data. Each row would include a date and time stamp alone with the userName. Column A is a unique key on the Active worksheet and the history worksheet would hold the hisotry of changed data. Any ideas would be great. Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Track changes by row in different worksheet - History tracking
Len,
Copy the code below, right click the sheet tab, select "View Code" and paste the code into the window that appears. Then put a blank sheet into your workbook, name it "History Sheet" (without the quotes), and any change made to a single cell of your first worksheet will be tracked. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... I am looking for code that would copy a row that a change is being made on to a separate worksheet. The data being copied would be the data before the change. I have found code that create tracking base on each change made to each cell...but I would like to have it base on the entire row of data. Each row would include a date and time stamp alone with the userName. Column A is a unique key on the Active worksheet and the history worksheet would hold the hisotry of changed data. Any ideas would be great. Thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Track changes by row in different worksheet - History tracking
Thanks....that works great. But is there any way to only write 1 record to
the History Sheet for any changes to that row for that session. For example, if I open the spread sheet and make changes to row 3 columns A, B, and C that would generate the 1 entry on the History sheet. So is there any way to put the Row number in a global variable so the application knows not to write that record again? "Bernie Deitrick" wrote: Len, Copy the code below, right click the sheet tab, select "View Code" and paste the code into the window that appears. Then put a blank sheet into your workbook, name it "History Sheet" (without the quotes), and any change made to a single cell of your first worksheet will be tracked. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... I am looking for code that would copy a row that a change is being made on to a separate worksheet. The data being copied would be the data before the change. I have found code that create tracking base on each change made to each cell...but I would like to have it base on the entire row of data. Each row would include a date and time stamp alone with the userName. Column A is a unique key on the Active worksheet and the history worksheet would hold the hisotry of changed data. Any ideas would be great. Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Track changes by row in different worksheet - History tracking
Len,
Try the version below. HTH, Bernie MS Excel MVP Dim myRows() As Long Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub On Error GoTo NotDimmed test = UBound(myRows) GoTo Dimmed NotDimmed: ReDim myRows(1 To 1) Dimmed: For i = 1 To UBound(myRows) If myRows(i) = Target.Row Then Exit Sub Next i ReDim Preserve myRows(1 To UBound(myRows) + 1) myRows(UBound(myRows)) = Target.Row With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... Thanks....that works great. But is there any way to only write 1 record to the History Sheet for any changes to that row for that session. For example, if I open the spread sheet and make changes to row 3 columns A, B, and C that would generate the 1 entry on the History sheet. So is there any way to put the Row number in a global variable so the application knows not to write that record again? "Bernie Deitrick" wrote: Len, Copy the code below, right click the sheet tab, select "View Code" and paste the code into the window that appears. Then put a blank sheet into your workbook, name it "History Sheet" (without the quotes), and any change made to a single cell of your first worksheet will be tracked. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... I am looking for code that would copy a row that a change is being made on to a separate worksheet. The data being copied would be the data before the change. I have found code that create tracking base on each change made to each cell...but I would like to have it base on the entire row of data. Each row would include a date and time stamp alone with the userName. Column A is a unique key on the Active worksheet and the history worksheet would hold the hisotry of changed data. Any ideas would be great. Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Track changes by row in different worksheet - History tracking
Thank you! That works great!
"Bernie Deitrick" wrote: Len, Try the version below. HTH, Bernie MS Excel MVP Dim myRows() As Long Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub On Error GoTo NotDimmed test = UBound(myRows) GoTo Dimmed NotDimmed: ReDim myRows(1 To 1) Dimmed: For i = 1 To UBound(myRows) If myRows(i) = Target.Row Then Exit Sub Next i ReDim Preserve myRows(1 To UBound(myRows) + 1) myRows(UBound(myRows)) = Target.Row With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... Thanks....that works great. But is there any way to only write 1 record to the History Sheet for any changes to that row for that session. For example, if I open the spread sheet and make changes to row 3 columns A, B, and C that would generate the 1 entry on the History sheet. So is there any way to put the Row number in a global variable so the application knows not to write that record again? "Bernie Deitrick" wrote: Len, Copy the code below, right click the sheet tab, select "View Code" and paste the code into the window that appears. Then put a blank sheet into your workbook, name it "History Sheet" (without the quotes), and any change made to a single cell of your first worksheet will be tracked. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... I am looking for code that would copy a row that a change is being made on to a separate worksheet. The data being copied would be the data before the change. I have found code that create tracking base on each change made to each cell...but I would like to have it base on the entire row of data. Each row would include a date and time stamp alone with the userName. Column A is a unique key on the Active worksheet and the history worksheet would hold the hisotry of changed data. Any ideas would be great. Thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Track changes by row in different worksheet - History tracking
You're welcome. Thanks for letting me know that you got it to work....
Bernie MS Excel MVP Thank you! That works great! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Track changes by row in different worksheet - History tracking
Bernie,
I may have spoke too soon.... the code does not seem to like when I type in a new row and then click to the next column. I am getting a Run-time error '1004': Method 'undo' of object '_Application' failed. For example if I open the Workbook and there are 5 rows of data, I type in row 6 column A and then click to column B, the error occurs. I can see occording to what I asked for there would not be a history created because there was nothing there when the workbook was open, but maybe in this case defaulting to the data written to column A or the entier row would help.....? Your thoughts? "LenJr" wrote: Thank you! That works great! "Bernie Deitrick" wrote: Len, Try the version below. HTH, Bernie MS Excel MVP Dim myRows() As Long Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub On Error GoTo NotDimmed test = UBound(myRows) GoTo Dimmed NotDimmed: ReDim myRows(1 To 1) Dimmed: For i = 1 To UBound(myRows) If myRows(i) = Target.Row Then Exit Sub Next i ReDim Preserve myRows(1 To UBound(myRows) + 1) myRows(UBound(myRows)) = Target.Row With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... Thanks....that works great. But is there any way to only write 1 record to the History Sheet for any changes to that row for that session. For example, if I open the spread sheet and make changes to row 3 columns A, B, and C that would generate the 1 entry on the History sheet. So is there any way to put the Row number in a global variable so the application knows not to write that record again? "Bernie Deitrick" wrote: Len, Copy the code below, right click the sheet tab, select "View Code" and paste the code into the window that appears. Then put a blank sheet into your workbook, name it "History Sheet" (without the quotes), and any change made to a single cell of your first worksheet will be tracked. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).Row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "LenJr" wrote in message ... I am looking for code that would copy a row that a change is being made on to a separate worksheet. The data being copied would be the data before the change. I have found code that create tracking base on each change made to each cell...but I would like to have it base on the entire row of data. Each row would include a date and time stamp alone with the userName. Column A is a unique key on the Active worksheet and the history worksheet would hold the hisotry of changed data. Any ideas would be great. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Some of the changes cannot be viewed in track changes history. | Setting up and Configuration of Excel | |||
| Track Changes History Worksheet | Excel Worksheet Functions | |||
| Please...need help setting up loss run history for claims tracking | Excel Discussion (Misc queries) | |||
| Tracking inventory order history | Excel Discussion (Misc queries) | |||
| How to export/save the track change history into another worksheet? | Excel Programming | |||
Hybrid Mode
