VBA Help - Create a running log of values entered in a cell

[wstbch]

Hi everyone,

I have researched and found similar codes to achieve a version of what am I looking for, but my VBA skills are not sufficient to make it custom to my need.
(Apologies, I cannot post a mini-sheet as my work blocks addons)

Column A has a list of permanent sites. Column B is where I enter the most recent inspection. For a clear historical log, I'm trying to have every value entered into Column B pushed to the next column and so on and so on...
Is this practical? Or is there a more practical application of this concept that I have missed.
Thanks in advance for your help.

 

[dreid1011]

If I were doing it, I would use column B as the data entry column (same as you are doing), but then immediately move the data to columns C and beyond instead of overwriting column B each time. But that is just preference. There are ways to do it as you are describing, but it feels more tedious to do that, even if it is only a couple more lines of code.

Book1 (version 1) 1-25-2024.xlsx
	A	B	C	D	E
1	Site Inspection Log
2	Site	Inspection	Most Recent Inspection	Previous Inspections
3	Site#1		1/1/2024	12/1/2023	11/1/2023
4	Site#2
5	Site#3
6	Site#4
Sheet9

Date entered in column B is immediately moved to C and the rest is shifted to the right.

 

[wstbch]

That is a great solution. I tried to copy the min-table you inserted, but when I paste it into excel there doesn't appear to be code attached. What am I doing wrong?

 

[dreid1011]

That is a great solution. I tried to copy the min-table you inserted, but when I paste it into excel there doesn't appear to be code attached. What am I doing wrong?

I have not provided any code yet. That was just an example of my idea. I will post code shortly.

 

[wstbch]

My bad. Thanks! Much appreciated

 

[dreid1011]

Insert the following code into the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B3:B6")) Is Nothing Then                '<--Update column B range here for as many sites you have.
    Target.Offset(, 1).Insert xlShiftToRight
    Range("C" & Target.Row()).Value = Range("B" & Target.Row()).Value
'    Range("C" & Target.Row()).Interior.Color = xlNone                  '<--use this line if column B has fill color you do not wish to transfer with the date.
    Range("B" & Target.Row()).ClearContents
End If
Application.EnableEvents = True
End Sub

 

[wstbch]

This is perfect! Thank you very much dreid1011

 

[dreid1011]

This is perfect! Thank you very much dreid1011

You're welcome, and happy to help.