can anybody help me figure this out

[sdc1234]

<!-- google_ad_section_start -->can anybody help me figure this out<!-- google_ad_section_end --> <!-- google_ad_section_start -->I am making a report to show about 2000 employees. I have a formula but it only works for 1 as well as it only pushes 1 entry then i have to restart the macro, I know there is an easy way to do this but I have been working on this for days and am all out of ideas, what I am trying to do is have a code that pushes or even saves previous job titles from different employees I have the history section and date section in AI through AZ, I have the current Job title and Date in Z and AA, I want to be able to do this without coping and pasting and I want the formula to start working as soon as I open the worksheet... Is there an easier way to do what I want to do or do I need to revamp the formula for about 2000 employees... Please help!!! here is the formula I have in "this workbook":
Sub MoveDate()

Select Case Range("AI3").Value
Case Is = ""
Range("Z3:AA3").Select
Selection.Cut
Range("AI3").Select
ActiveSheet.Paste

Case Else

Range("AI3").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("AK3").Select
ActiveSheet.PasteSpecial Format:=12, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

Range("Z3:AA3").Select
Selection.Cut
Range("AI3").Select
ActiveSheet.Paste
End Select
End Sub

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[sdc1234]

everything works perfect except I don't want whoever is running this report to have to every time a date and job title is entered to go into the vb editor and hit the run button.. is there another way to do this check your PM
please advise
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Warship]

The Worbook_Open runs only once when the workbook is open.

MoveDates processes all rows in "Z" each time it runs. If you want to update the history per entry,
it doesn't make sense to process all rows for each entry. Unless I'm missing something.

To update per entry we will use the Worksheet Change Event. This has MINIMAL user error checking.

Place this in the Sheet1 Module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 26 Or Target.Column = 27) And Target.Row > 2 Then
        Dim r As Long
        r = Target.Row
        If Range("Z" & r) <> "" And Range("AA" & r) <> "" Then
            If Not IsDate(Range("AA" & r)) Then
                MsgBox "Invalid Date", vbOKOnly, "ERROR"
                Range("AA" & r).Select
                Exit Sub
            End If
            If MsgBox("Is this correct?" & vbLf & vbLf & _
                Range("Z" & r) & " " & Range("AA" & r), vbYesNo, _
                "CONFIRM ENTRY") = vbYes Then
                Range("Z" & r & ":AA" & r).Copy
                Range("AI" & r).Insert Shift:=xlToRight
                Application.CutCopyMode = False
            End If
        End If
    End If
End Sub

 

[sdc1234]

It is working I just wanted to thank u I really appreciate all your help with this project
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Warship]

You are Welcome.

 

[sdc1234]

Here is the tricky part can we do this without using macros