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]

I apologize about no making things clear<!-- google_ad_section_end --> <!-- google_ad_section_start -->I am trying to explain the best I can, if u have any ideas that would be helpful..


I need to find a way to store multiple job titles for a single employee in a single column in a job history section, I have about 2000 different employees with job titles that change daily, I want to enter the new job title and have the old job title move over 5 or 6 cells to the job history section I know no other easier way of explaining this


please help

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

 

[Warship]

Is this what you want to end up with?

Excel Workbook
	Z	AA	AI	AJ	AK	AL	AM	AN
1
2	Current Title	Date	Old Title	Date	Older Title	Date	Oldest Title	Date
3	Manager	1/4/2011	CSM	1/3/2011	Cashier	1/2/2011	Bagger	1/1/2011
Sheet1

Excel 2007
And you want to trigger the history move by changing "Z3" & "AA3"?

 

[sdc1234]

I am trying to come up with a way to track people and what they r doing and what they have done there r bout 2000 people I am trying to do this all in the same row, if there is an easier way to do this please let me know
Thx

 

[sdc1234]

I am trying to come up with a way to track people and what they r doing and what they have done there r bout 2000 people I am trying to do this all in the same row
Thx

Yes that is correct

As well as z4-z2000 and aa4-aa2000

 

[Warship]

If you start with this:

Excel Workbook
	Z	AA	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
2	Current Title	Date	Current	Date	Old	Date	Older	Date	Oldest	Date	Oldest	Date
3	CSM	1/3/2011	CSM	1/3/2011	Cashier	1/2/2011	Bagger	1/1/2011
4	Bagger	1/1/2011	Bagger	1/1/2011
5	Owner	1/4/2011	Owner	1/4/2011	CSM	1/3/2011	Cashier	1/2/2011	Bagger	1/1/2011
Start With

Excel 2007

And make these changes:

Excel Workbook
	Z	AA	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
2	Current Title	Date	Current	Date	Old	Date	Older	Date	Oldest	Date	Oldest	Date
3	Owner	1/4/2011	CSM	1/3/2011	Cashier	1/2/2011	Bagger	1/1/2011
4	Bagger	1/1/2011	Bagger	1/1/2011
5	Bagger	1/1/2011	Owner	1/4/2011	CSM	1/3/2011	Cashier	1/2/2011	Bagger	1/1/2011
Make Changes

Excel 2007

Then run "MoveDate"

Sub MoveDate()
    Dim LR As Long
    For LR = 3 To Cells(Rows.Count, "Z").End(xlUp).Row 'lastrow
        If Range("Z" & LR)<> Range("AI" & LR) Then
            Range("Z" & LR & ":AA" & LR).Copy
            Range("AI" & LR).Insert Shift:=xlToRight
        End If
    Next LR
    Application.CutCopyMode = False
End Sub

You will end up with:

Excel Workbook
	Z	AA	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
2	Current Title	Date	Current	Date	Old	Date	Older	Date	Oldest	Date	Oldest	Date
3	Owner	1/4/2011	Owner	1/4/2011	CSM	1/3/2011	Cashier	1/2/2011	Bagger	1/1/2011
4	Bagger	1/1/2011	Bagger	1/1/2011
5	Bagger	1/1/2011	Bagger	1/1/2011	Owner	1/4/2011	CSM	1/3/2011	Cashier	1/2/2011	Bagger	1/1/2011
After "MoveDates"

Excel 2007

Am I on the right track?
All rows that have a value in col "Z" will process

 

[sdc1234]

Pretty much except I need the cols in z and aa to process job title and date

 

[Warship]

They do.

 

[sdc1234]

I will try the code out when I get back to the office i will let u know the outcome I appreciate all ur help

 

[Warship]

If col "Z" is the only col with Current Position and you change it, then you have nothing to compare to to determine if there has been a change.

That is why I also incuded Current Position in "AI".

If you cannot get Current Position into col "AI", then we will have to solve this another way.

It is possible, but more involved.

Also I suggest downloading and installing this AddIn to post sheet samples:
http://www.mrexcel.com/forum/showpost.php?p=2545970&postcount=2

 

[sdc1234]

Do I need to save this as a Macro file or a regular excel file