You are here:

Excel/Generate List of EACH changes in other Sheet

Advertisement


Question
QUESTION: Hi Tom,

There are 5 cells(B1:f1).
Some of them have numbers and some have text.
Depending on the number I enter in Cell A1, numbers in those 5 cells might change.

I want to track each changes, if happens in those 5 cells, in other sheet.

Can you help?
Thanks

ANSWER: Parantapkumar,

Many challenges here.  The primary challenge is that you would probably need to use an event macro to write the changed data to the other sheet.  You could use the change event for cell A1 in the original sheet.  Since you say numbers might change, then you would need to keep track of what the current values in the cells were before you made the change.  Anyway, since your needs are incomplete in terms of exact details, I will provide this starter code as an example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, lastrow As Long
On Error GoTo ErrHandler
If Target.Address = "$A$1" Then
Application.EnableEvents = False
 Set sh = Worksheets("sheet2")
 lastrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
 sh.Cells(lastrow, 1).Resize(1, 6).Value = Me.Range("A1").Resize(1, 6).Value
End If
ErrHandler:
Application.EnableEvents = True
End Sub

If you put that in the sheet module where you will be changing value in cell A1, then that will write the values in A1:F1 in the next available row in Sheet2 each time you manually enter a value in A1 of the sheet with the code.

You right click on the sheet tab and select view code and then paste in the above code.

I have not attempted to determine if any of the values in B1:F1 have actually changed.  If at least one will always change, then this would be a good start.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom,
Thanks for the code you gave it is also useful for other purposes.

I want to track each changes, "if happens" in any of those 5 cells, in other sheet.
Suppose,
Scene1:
in A1 there is a random number. This time it is 5.

In those 5 cells, Text Number1 Text Text Number2.
Now Number1 increases its value when it finds the A1 dividable 2. But this time it is 5 so it will remain as it is (say it is 89).
Number2 increases its value when it finds the A1 in multiple of 7. So it won't change. It will remain as it (say it is 54).
Listing event in other Sheet: Event1: Text 89 Text Text 54.

Scene2:
in A1 there is a number 6.

Number1 will become 90 because 6 is divisible by 2.
Number2 will remail same 54.
Listing event in other Sheet: Event2: Text 90 Text Text 54.

scene3:
in A1 there is a number 14.

Number1 will become 91 because 14 is divisible by 2.
Number2 will become 55 as the number 14 is in multiple of 7.
Listing event in other Sheet: Event3: Text 91 Text Text 55.

Scene4:
in A1 there is a number 15.

Number1 that is 91, will remain as it is  because 15 is not divisible by 2.
Number2 that is 55 will remain as it is because the number 15 is not in multiple of 7.
SO THIS SCENE DOES NOT CHANGE ANY OF 5 CELLS, HENSE IT IS NOT AN EVENT.

Scene5:
in A1 there is a number 19.

Number1 that is 91, will remain as it is  because 19 is not divisible by 2.
Number2 that is 55 will remain as it is because the number 19 is not in multiple of 7.
SO THIS SCENE DOES NOT CHANGE ANY OF 5 CELLS, HENSE IT IS NOT AN EVENT.

Scene6:
in A1 there is a number 21.

Number1 that is 91, will remain as it is  because 21 is not divisible by 2.
Number2 will now become 56 because the number 21 is in multiple of 7.
So this scene will make an event.
Listing event in other Sheet: Event4: Text 91 Text Text 56.

So list in other sheet is as follows.
Event1: Text 89 Text Text 54.
Event2: Text 90 Text Text 54.
Event3: Text 91 Text Text 55.
Event4: Text 91 Text Text 56.

As You Can Notice That There Are 6 Scenes But Listed Are Only 4 Events. Because 2 Of The Scenes Didn't Change Any Of Those 5 Cells Hence No Events Was Created At That Time.

I hope I have cleared the question.
Thanks

Answer
Parantapkumar,
So the code as written would do that now except it would list each event.  You could write simple formula to identify those "bad" rows by comparing to the row above and marking those Rows.  Then you could filter on the marks and delete those rows all at once.

If you wanted to modify the code to avoid writing a duplicate row you could do

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet, lastrow As Long, I as long, bMatch as Boolean
On Error GoTo ErrHandler
If Target.Address = "$A$1" Then
Application.EnableEvents = False
 Set sh = Worksheets("sheet2")
 lastrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
 bMatch = True
 ' check if duplicate
 for I = 2 to 6
   if sh.cells(lastrow - 1, I).Text <> sh.cells(1, I).Text then
       bMatch = False
       exit for
   end if
 Next
 if not bMatch then
 sh.Cells(lastrow, 1).Resize(1, 6).Value = Me.Range("A1").Resize(1, 6).Value
 end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Not tested so you might need to tweak the code - but it should be close to what you want.
Also it assumes formatting in both sheets will be identical

--
Regards,
Tom Ogilvy
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.