How To Remember Or Save Previous Cell Value Of A Changed Cell In Excel?

[mathurjan]

I have something similar issues!

Lets say I have B5 - which is my car number and C5 which is deck block last changed date. in the image you can see i named this first sheet main sheet and i have made individual taps for each heading.

the question is every time i change the date on the main sheet on C5 i wan the history of the date to be saved on the individual tap "deck Blocks" - B5,C5,D5 and so on .......

i wan this done for whole sheet for each tap.


I've been trying to learn the Vcode and its a night mare for me.


thank you guys

 

[DanteAmor]

Try this code in event sheet "Main sheet"

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Target.Address(0, 0) = "C5" Then
    Dim lc As Long
    With Sheets("deck Blocks")
      lc = .Cells(5, Columns.Count).End(xlToLeft).Column + 1
      .Cells(5, lc).Value = Target.Value
    End With
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook

 

[mathurjan]

Try this code in event sheet "Main sheet"

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Target.Address(0, 0) = "C5" Then
    Dim lc As Long
    With Sheets("deck Blocks")
      lc = .Cells(5, Columns.Count).End(xlToLeft).Column + 1
      .Cells(5, lc).Value = Target.Value
    End With
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook

you are a genius star Dante Amor, You made my life so much easier


thank you so much

 

[mathurjan]

Try this code in event sheet "Main sheet"

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  If Target.Address(0, 0) = "C5" Then
    Dim lc As Long
    With Sheets("deck Blocks")
      lc = .Cells(5, Columns.Count).End(xlToLeft).Column + 1
      .Cells(5, lc).Value = Target.Value
    End With
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook

is there a way to apply this setting for all the taps in that sheet in that coding ?

 

[DanteAmor]

you are a genius star Dante Amor, You made my life so much easier


thank you so much

I'm glad to help you. Thanks for the feedback.

 

[DanteAmor]

is there a way to apply this setting for all the taps in that sheet in that coding ?

You would have to explain on which sheets and where they should put their result.

 

[mathurjan]

You would have to explain on which sheets and where they should put their result.

hi dante is there a way to upload my file in here, i tried but i might be missing some steps, it will be easier if i send you the file and you can have a look from your side!

 

[mathurjan]

Hi Dante,

hope this helps, if its alot of work dont worry i will try fiddle around with the formula, but this is the whole setup i would need tbh,


thanks again

For deck block:
main sheet - C5 to C144
Deck Block sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Wall Blocks
main sheet - D5 to D144
Wall Block sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Viaduct Block
main sheet - E5 to E144
Viaduct Block sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Center Deck
main sheet - F5 to F144
Center Deck sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Center Bolts
main sheet - G5 to G144
Center bolts sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Front or trailing edge Plates
main sheet - H5 to H144
Front or trailing edge Plates sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Sand Skirts
main sheet - I5 to I144
Sand Skirts sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Restraining bar
main sheet - J5 to J144
Restraining bar sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Wheel change
main sheet - K5 to K144
Wheel Change sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Wheel grease
main sheet - L5 to L144
wheel grease sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Leading Rope
main sheet - M5 to M144
Leading Rope sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Trailing Rope
main sheet - N5 to N144
Trailing Rope sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

For Full car Strip
main sheet - O5 to O144
Full car Strip sheet B5 to B144 like we did for that one cell but i want it for all the car numbers

 

[DanteAmor]

hi dante is there a way to upload my file in here, i tried but i might be missing some steps, it will be easier if i send you the file and you can have a look from your side!

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[mathurjan]

hi Dante, hope you had a great weekend, sorry for the delay in replying,

I was away from office over the weekends,

Dropbox - File Deleted - Simplify your life

www.dropbox.com

see if this is any helpful,

Mathu