Excel move data between sheets automatically

[ExcellNewbie98]

Hi all,

Complete Excel and VBA newbie here I'm afraid.

I'm trying to AUTOMATICALLY move a row of data from one sheet titled "FUTURE JOBS" to another sheet titled "COMPLETED JOBS or CURRENT JOBS". I have a table of data that is constantly expanding or shrinking in the FUTURE JOBS, COMPLETED JOBS and CURRENT JOBS sheet that goes from columns A-J and starts with headers in row 1 (so first row of actual data is in row 3). I need the data to start copying into EMPTY rows on the "COMPLETED JOBS, FUTURE JOBS or CURRENT JOBS" sheet starting in row 3, column A on all of them. I need this to run automatically every time the information in columns A-J (in any cell) is updated. And I need it to only move when the cells in column I of the "FUTURE JOBS" sheet read "Completed jobs, future jobs or current jobs". I need to be able to have three selections per cell 'FUTURE JOBS', 'CURRENT JOBS', 'COMPLETED JOBS' to enable this.

I want to be able to shift jobs between the three sheets as they change progress.

I have attached a photo below for reference. Please let me know if you have any questions and I will respond promptly.

Thank you so much for your help in advance!! It really is greatly appreciated!

Kind regards,

 

[My Aswer Is This]

You said:
And I need it to only move when the cells in column I of the "FUTURE JOBS" sheet read "Completed jobs, future jobs or current jobs

So, if on the sheet named Future Jobs the value in column I says "Future jobs" why do we need to move it to sheet named Future Jobs??

 

[ExcellNewbie98]

Apologies, the aim would be for the job status could be changed as it goes through the process from future to completed, therefore it would need to shift to the required sheet depending on it's selection

 

[My Aswer Is This]

Apologies, the aim would be for the job status could be changed as it goes through the process from future to completed, therefore it would need to shift to the required sheet depending on it's selection

My script would run when you enter a value in Column I of the sheet named Completed Jobs.
It would copy the row to the other sheet depending on sheet name. It would never copy a row into sheet named Completed Jobs.

Or are you saying we need to put a script in all three sheets.

 

[ExcellNewbie98]

Good morning,

So what it needs is for the jobs to be inputted into future jobs.

They will then be sorted into job type using column I into either 'Future' (No movement), 'Current' (move to current job sheet) or 'Completed' (move to completed jobs sheet).

I want to then be able to move a 'completed job' or a 'current job' back into the future or current sheets if there's a hold up with a project or if it ends up needing more work/someone assigns it to the wrong job status.

Does that maker any more sense?

 

[My Aswer Is This]

You said:
I need this to run automatically every time the information in columns A-J (in any cell) is updated. And I need it to only move when the cells in column I of the "FUTURE JOBS" sheet read "Completed jobs, future jobs or current jobs"

So, when any cell in columns A To J changes you need to look in column I and if it says one of sheet names to move this sheet to another sheet.

So, if user enters "apple" in range B2
the script needs to look in column I of same row and if a certain value is in column I copy this row to another sheet.
I'm not able to write a script to do this. Maybe someone else here can help you.

 

[My Aswer Is This]

Most times when I help others on such a request they say when In column A I enter "Master" I want this row copied to sheet named "Master" but what your wanting is much more complicated.
You want a script to run any time you change any value in a Columns A To J

 

[ExcellNewbie98]

Apologies again, I don’t need it to run every time a cell is updated. I only need it to update if any of the cells in coloumn I are changed between the job status.

I wouldn’t need it to re run every time a cell is changed on the rest of the sheet.

I’m sorry for mixing that info up.

 

[My Aswer Is This]

Apologies again, I don’t need it to run every time a cell is updated. I only need it to update if any of the cells in coloumn I are changed between the job status.

I wouldn’t need it to re run every time a cell is changed on the rest of the sheet.

I’m sorry for mixing that info up.

If this going to be a case where this row might me moved from sheet named Alpha to sheet named Bravo and then later the same row might be moved from sheet Named "Bravo" and then moved back to sheet named "Alpha"
And then you want this same type of code in sheet named Bravo to copy this row to sheet named "Alpha" and are these rows to be deleted from the original sheet

 

[SQUIDD]

Hi

So if i understand you correctly

Column J on any of your 3 tabs could contain the words "CURRENT JOBS" or "COMPLETED JOBS" or "FUTURE JOBS"
And column J on any of your 3 tabs is changed manually by the user to these key words.

So for instance, after the user changes the status it should hop from the sheet it is currently on, to the new sheet as per the change.?

if so, try the code below, put it in a standard module. Run it on a test data set 1st.

If it works, we just need to add in the sheet module to call the macro when your column changes on any of the 3 sheets

Sub SORT_MY_JOBS()
    'COMPLETED
    LR_COMPLETED = Range("'COMPLETED JOBS'!A" & Rows.Count).End(xlUp).Row
    For A = LR_COMPLETED To 3 Step -1
        If Range("'COMPLETED JOBS'!J" & A) = "CURRENT JOBS" Then
            Range("'COMPLETED JOBS'!A" & A & ":J" & A).Copy
            LR_CURRENT = Range("'CURRENT JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            Range("'CURRENT JOBS'!A" & LR_CURRENT).PasteSpecial
            Range("'COMPLETED JOBS'!J" & A).EntireRow.Delete
        End If
        If Range("'COMPLETED JOBS'!J" & A) = "FUTURE JOBS" Then
            Range("'COMPLETED JOBS'!A" & A & ":J" & A).Copy
            LR_FUTURE = Range("'FUTURE JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            Range("'FUTURE JOBS'!A" & LR_FUTURE).PasteSpecial
            Range("'COMPLETED JOBS'!J" & A).EntireRow.Delete
        End If
    Next A
    'CURRENT
    LR_CURRENT = Range("'CURRENT JOBS'!A" & Rows.Count).End(xlUp).Row
    For B = LR_CURRENT To 3 Step -1
        If Range("'CURRENT JOBS'!J" & B) = "COMPLETED JOBS" Then
            Range("'CURRENT JOBS'!A" & B & ":J" & B).Copy
            LR_COMPLETED = Range("'COMPLETED JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            Range("'COMPLETED JOBS'!A" & LR_COMPLETED).PasteSpecial
            Range("'CURRENT JOBS'!J" & B).EntireRow.Delete
        End If
        If Range("'CURRENT JOBS'!J" & B) = "FUTURE JOBS" Then
            Range("'CURRENT JOBS'!A" & B & ":J" & B).Copy
            LR_FUTURE = Range("'FUTURE JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            Range("'FUTURE JOBS'!A" & LR_FUTURE).PasteSpecial
            Range("'CURRENT JOBS'!J" & B).EntireRow.Delete
        End If
    Next B
    'FUTURE
    LR_FUTURE = Range("'FUTURE JOBS'!A" & Rows.Count).End(xlUp).Row
    For C = LR_FUTURE To 3 Step -1
        If Range("'FUTURE JOBS'!J" & C) = "CURRENT JOBS" Then
            Range("'FUTURE JOBS'!A" & C & ":J" & C).Copy
            LR_CURRENT = Range("'CURRENT JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            Range("'CURRENT JOBS'!A" & LR_CURRENT).PasteSpecial
            Range("'FUTURE JOBS'!J" & C).EntireRow.Delete
        End If
        If Range("'FUTURE JOBS'!J" & C) = "COMPLETED JOBS" Then
            Range("'FUTURE JOBS'!A" & C & ":J" & C).Copy
            LR_COMPLETED = Range("'COMPLETED JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            Range("'COMPLETED JOBS'!A" & LR_COMPLETED).PasteSpecial
            Range("'FUTURE JOBS'!J" & C).EntireRow.Delete
        End If
    Next C
End Sub