Excel move data between sheets automatically

ExcellNewbie98

New Member
Joined
Jul 6, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.
Capture.PNG


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,
 
Slight change, i forgot to account for your data never to be in row 2

Code below

but also your sheet module code to trigger the macro is also below

SHEET MODULE CODE FOR ALL 3 SHEETS

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 10 Then SORT_MY_JOBS
End Sub



STANDARD MODULE CODE
VBA Code:
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
            If LR_CURRENT = 2 Then LR_CURRENT = 3
            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
            If LR_FUTURE = 2 Then LR_FUTURE = 3
            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
    If LR_CURRENT = 2 Then LR_CURRENT = 3
    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
            If LR_COMPLETED = 2 Then LR_COMPLETED = 3
            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
            If LR_FUTURE = 2 Then LR_FUTURE = 3
            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
    If LR_FUTURE = 2 Then LR_FUTURE = 3
    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
            If LR_CURRENT = 2 Then LR_CURRENT = 3
            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
            If LR_COMPLETED = 2 Then LR_COMPLETED = 3
            Range("'COMPLETED JOBS'!A" & LR_COMPLETED).PasteSpecial
            Range("'FUTURE JOBS'!J" & C).EntireRow.Delete
        End If
    Next C
End Sub
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
SORRY, JUST CHECKED YOUR DATA SET, HAD IT A LITTLE WRONG

VBA Code:
       Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then SORT_MY_JOBS
End Sub

VBA Code:
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'!I" & A) = "CURRENT JOBS" Then
            Range("'COMPLETED JOBS'!A" & A & ":J" & A).Copy
            LR_CURRENT = Range("'CURRENT JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_CURRENT = 2 Then LR_CURRENT = 3
            Range("'CURRENT JOBS'!A" & LR_CURRENT).PasteSpecial
            Range("'COMPLETED JOBS'!J" & A).EntireRow.Delete
        End If
        If Range("'COMPLETED JOBS'!I" & A) = "FUTURE JOBS" Then
            Range("'COMPLETED JOBS'!A" & A & ":J" & A).Copy
            LR_FUTURE = Range("'FUTURE JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_FUTURE = 2 Then LR_FUTURE = 3
            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
    If LR_CURRENT = 2 Then LR_CURRENT = 3
    For B = LR_CURRENT To 3 Step -1
        If Range("'CURRENT JOBS'!I" & B) = "COMPLETED JOBS" Then
            Range("'CURRENT JOBS'!A" & B & ":J" & B).Copy
            LR_COMPLETED = Range("'COMPLETED JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_COMPLETED = 2 Then LR_COMPLETED = 3
            Range("'COMPLETED JOBS'!A" & LR_COMPLETED).PasteSpecial
            Range("'CURRENT JOBS'!J" & B).EntireRow.Delete
        End If
        If Range("'CURRENT JOBS'!I" & B) = "FUTURE JOBS" Then
            Range("'CURRENT JOBS'!A" & B & ":J" & B).Copy
            LR_FUTURE = Range("'FUTURE JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_FUTURE = 2 Then LR_FUTURE = 3
            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
    If LR_FUTURE = 2 Then LR_FUTURE = 3
    For C = LR_FUTURE To 3 Step -1
        If Range("'FUTURE JOBS'!I" & C) = "CURRENT JOBS" Then
            Range("'FUTURE JOBS'!A" & C & ":J" & C).Copy
            LR_CURRENT = Range("'CURRENT JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_CURRENT = 2 Then LR_CURRENT = 3
            Range("'CURRENT JOBS'!A" & LR_CURRENT).PasteSpecial
            Range("'FUTURE JOBS'!J" & C).EntireRow.Delete
        End If
        If Range("'FUTURE JOBS'!I" & C) = "COMPLETED JOBS" Then
            Range("'FUTURE JOBS'!A" & C & ":J" & C).Copy
            LR_COMPLETED = Range("'COMPLETED JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_COMPLETED = 2 Then LR_COMPLETED = 3
            Range("'COMPLETED JOBS'!A" & LR_COMPLETED).PasteSpecial
            Range("'FUTURE JOBS'!J" & C).EntireRow.Delete
        End If
    Next C
End Sub
 
Upvote 0
SORRY, JUST CHECKED YOUR DATA SET, HAD IT A LITTLE WRONG

VBA Code:
       Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then SORT_MY_JOBS
End Sub

VBA Code:
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'!I" & A) = "CURRENT JOBS" Then
            Range("'COMPLETED JOBS'!A" & A & ":J" & A).Copy
            LR_CURRENT = Range("'CURRENT JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_CURRENT = 2 Then LR_CURRENT = 3
            Range("'CURRENT JOBS'!A" & LR_CURRENT).PasteSpecial
            Range("'COMPLETED JOBS'!J" & A).EntireRow.Delete
        End If
        If Range("'COMPLETED JOBS'!I" & A) = "FUTURE JOBS" Then
            Range("'COMPLETED JOBS'!A" & A & ":J" & A).Copy
            LR_FUTURE = Range("'FUTURE JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_FUTURE = 2 Then LR_FUTURE = 3
            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
    If LR_CURRENT = 2 Then LR_CURRENT = 3
    For B = LR_CURRENT To 3 Step -1
        If Range("'CURRENT JOBS'!I" & B) = "COMPLETED JOBS" Then
            Range("'CURRENT JOBS'!A" & B & ":J" & B).Copy
            LR_COMPLETED = Range("'COMPLETED JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_COMPLETED = 2 Then LR_COMPLETED = 3
            Range("'COMPLETED JOBS'!A" & LR_COMPLETED).PasteSpecial
            Range("'CURRENT JOBS'!J" & B).EntireRow.Delete
        End If
        If Range("'CURRENT JOBS'!I" & B) = "FUTURE JOBS" Then
            Range("'CURRENT JOBS'!A" & B & ":J" & B).Copy
            LR_FUTURE = Range("'FUTURE JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_FUTURE = 2 Then LR_FUTURE = 3
            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
    If LR_FUTURE = 2 Then LR_FUTURE = 3
    For C = LR_FUTURE To 3 Step -1
        If Range("'FUTURE JOBS'!I" & C) = "CURRENT JOBS" Then
            Range("'FUTURE JOBS'!A" & C & ":J" & C).Copy
            LR_CURRENT = Range("'CURRENT JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_CURRENT = 2 Then LR_CURRENT = 3
            Range("'CURRENT JOBS'!A" & LR_CURRENT).PasteSpecial
            Range("'FUTURE JOBS'!J" & C).EntireRow.Delete
        End If
        If Range("'FUTURE JOBS'!I" & C) = "COMPLETED JOBS" Then
            Range("'FUTURE JOBS'!A" & C & ":J" & C).Copy
            LR_COMPLETED = Range("'COMPLETED JOBS'!A" & Rows.Count).End(xlUp).Row + 1
            If LR_COMPLETED = 2 Then LR_COMPLETED = 3
            Range("'COMPLETED JOBS'!A" & LR_COMPLETED).PasteSpecial
            Range("'FUTURE JOBS'!J" & C).EntireRow.Delete
        End If
    Next C
End Sub
Thank you! Just about to test it, the column I is what the value 'Completed, future and current'. Is that how you've put it in the code?
 
Upvote 0
In your original post you said:
I'm trying to AUTOMATICALLY move a row of data from one sheet titled "FUTURE JOBS"
Move a Row.
Sounds like to me move the entire row.
So why are the scripts i see here copying range A To J
Why not just say copy entire row?
 
Upvote 0
I have attached a revised screenshot of the sheet, each cell in column 'I' contains a drop down option.
When this drop down is changed to reflect the status of the job the entire row of data needs to be moved across to the corresponding sheet. If the sheet name matches with the drop down option then the row should just stay put.

This needs to run only when the data in the drop down cells in column 'I' change.

I'm sorry I am very new to this and I have been quite confusing to you all.

Thank you for all your help.

DES PV Install Tracker Test.xlsm
ABCDEFGHIJK
1DES PV Instals
2Customer NameDate of InstallPV cells/BatteryInverterRoof TypeScaffolding Required10% DepositPaidJob StatusNotes
3Smith2/11/21xxThatchYesPaidYesFuture 2 x weeks late
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Future Jobs
Cells with Data Validation
CellAllowCriteria
I3:I25List=$P$3:$P$5
B3:B811List=CategoryList
C3:C811List=EmployeeList
A2Any value
B2Any value
C2Any value
D2Any value
E2:F2Any value
G2Any value
H2:I2Any value
J2Any value
 
Upvote 0
Thank you! Just about to test it, the column I is what the value 'Completed, future and current'. Is that how you've put it in the code?
I used COMPLETED JOBS, FUTURE JOBS AND CURRENT JOBS as the key words
 
Upvote 0
In your original post you said:
I'm trying to AUTOMATICALLY move a row of data from one sheet titled "FUTURE JOBS"
Move a Row.
Sounds like to me move the entire row.
So why are the scripts i see here copying range A To J
Why not just say copy entire row?
MY ANSWER IS THIS- I just coded to the screen shot in the OP, enitre row or range i though could be easily ammended if required, however, in the OP the screenshot have no dadat beyond column J.
 
Upvote 0
excelnewbie98,

Is this now working as desired?

dave
 
Last edited by a moderator:
Upvote 0
Here is how I suggest doing it:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

The script runs when you enter a sheet name in column 9
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  7/8/2022  3:14:08 PM  EDT
If Target.Column = 9 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
On Error GoTo M
Application.ScreenUpdating = False
Dim ans As String
Dim r As Long
Dim Lastrow As Long
r = Target.Row
ans = Target.Value
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Rows(Lastrow)
End If
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "You Entered  " & Target.Value & vbNewLine & "We have no sheet by that name"
End Sub
 
Upvote 0
Hi,

I attached a photo, it's not working. I'm assuming I've done something wrong -
excelnewbie98, you have marked you on post as a solution that contains just a "-"

Is this now working as desired?

dave
 

Attachments

  • Screenshot 2022-07-08 at 20.17.24.jpg
    Screenshot 2022-07-08 at 20.17.24.jpg
    237.3 KB · Views: 13
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top