VBA Help Please

V

victorski

New Member
Joined
Nov 27, 2024
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hello brains trust,

I'm hoping someone can help me.

I have a workbook that I use to manage a project - I have 3 sheets, 'Overdue' (Sheet3), 'Priority Master List' (Sheet4) and 'Completed' (Sheet10)

The priority master list has activities listed in it - I created a macro that copies an entire row to the 'Overdue' when 'Days Left' is less than or equal to '5'.

Sub MoveData()
Sheet4.Range("A1:G1").AutoFilter
Sheet4.[G1:G150].AutoFilter Field:=7, Criteria1:="<=5"
Sheet4.[A1:G150].Copy Sheet3.[A1]
Sheet4.[A1].AutoFilter
End Sub

This seems to work good for my purposes.

I would also like the macro to move rows to the 'Completed' sheet and then delete it from the 'Master Priority List' when a date inputted into the 'END' field.

Could anyone please help me with this?

I also have a manager who reviews my workbook and doesn't know how to run the macro - is it possible to program it run automatically rather than manually running it?

Any help would be greatly appreciated.

Victor

Priority Master List Mini-sheet:

IDLMP Works Schedule EXTRA TEST.xlsm
ABCDEFGHI
1PriorityTaskCommentsASSIGNED TOPROGRESSDUEDAYS LEFTSTARTEND
2
3110%31/12/202433
41.10%15/12/202417
5110%31/12/202433
61.20%15/12/202417
71.20%20/01/202553
81.20%20/01/202553
91.20%15/12/202417
101.30%20/12/202422
111.40%31/12/2025398
121.40%15/12/202417
131.40%20/01/202553
141.40%20/01/202553
151.40%15/12/202417
161.40%1/03/202593
171.55%31/12/202433
181.50%15/12/202417
191.50%20/01/202553
201.50%20/01/202553
211.595%15/12/202417
221.50%15/11/2024-13
231.50%15/11/2024-13
241.520%6/11/2024-22
251.510%30/10/2024-29
261.50%23/11/2024-5
27
282.10%31/12/202433
292.10%7/11/2024-21
302.10%20/01/202553
312.20%31/12/20271128
322.30%31/12/20271128
332.40%31/12/202433
342.50%31/12/20271128
35
363.10%12/03/2025104
373.20%11/02/202575
383.30%3/03/202595
39
404.10%3/12/20245
414.10%8/10/2024-51
424.10%8/11/2024-20
434.20%
444.30%
454.40%3/12/20245
464.40%8/10/2024-51
474.40%8/11/2024-20
484.50%14/01/202547
494.60%
505
515.1100%24/07/202424/07/2024
525.20%3/03/202595
535.295%15/10/2024-4415/08/202424/10/2024
545.20%
555.20%31/10/2024-28
565.20%15/12/202417
575.290%31/10/2024-28
585.20%5/12/20247
595.20%5/12/20247
605.20%10/12/202412
615.20%20/01/202553
625.30%3/03/202595
636
646.190%4/12/20246
Priority Master List
Cell Formulas
RangeFormula
G3:G26,G64,G55:G62,G52:G53,G45:G48,G40:G42,G36:G38,G28:G34G3=F3-TODAY()
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I5Expression=AND($G5<>"",$G5<=5)textNO
I3Expression=AND($G3<>"",$G3<=5)textNO
I10Expression=AND($G10<>"",$G10<=5)textNO
I4Expression=AND($G4<>"",$G4<=5)textNO
I6:I9Expression=AND($G6<>"",$G6<=5)textNO
E64Other TypeDataBarNO
E64Expression=AND($G64<>"",$G64<=5)textNO
E28Other TypeDataBarNO
E31:E34Other TypeDataBarNO
E55:E56Other TypeDataBarNO
E57:E62,E51:E54Other TypeDataBarNO
E29:E30Other TypeDataBarNO
D67:I154,D14:I63,D3:H13,I11:I13Expression=AND($G3<>"",$G3<=5)textNO
E18:E20Other TypeDataBarNO
E21:E26,D63,E35:E49,E3:E17,E54Other TypeDataBarNO



Overdue Mini-sheet:
IDLMP Works Schedule EXTRA TEST.xlsm
ABCDEFG
1PriorityTaskCommentsASSIGNED TOPROGRESSDUEDAYS LEFT
21.50%15/11/2024-13
31.50%15/11/2024-13
41.520%6/11/2024-22
51.510%30/10/2024-29
61.50%23/11/2024-5
72.10%7/11/2024-21
84.10%3/12/20245
94.10%8/10/2024-51
104.10%8/11/2024-20
114.40%3/12/20245
124.40%8/10/2024-51
134.40%8/11/2024-20
14595%15/10/2024-44
1550%31/10/2024-28
16590%31/10/2024-28
Overdue
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E15Other TypeDataBarNO
E14,E16Other TypeDataBarNO
E7Other TypeDataBarNO
D19:G104,D2:G16Expression=AND($G2<>"",$G2<=5)textNO
E8:E13,E2:E6Other TypeDataBarNO


Completed Mini-sheet:
IDLMP Works Schedule EXTRA TEST.xlsm
ABCDEFGHI
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Completed
 

Attachments

  • VBA Help.PNG
    VBA Help.PNG
    23.9 KB · Views: 2
Sort by date Sort by votes
Hi Fuji,

Thank you so much - it's almost perfect now :)

I have two small issues:

1) If the value of 'G' on Master Priority List is less than or equal to 5 it is then copied to Overdue. If I then put a date in the 'I' column on the Master Priority List, it will copy it to Completed, delete it from the Master Priority List but it remains on Overdue. How can I get it to delete it from both sheets?

2) When I change 'F' (Due Date), it automatically changes 'G' (Days Left) which uses the formula '=F3-TODAY()' but if that value is now >5 days it should disappear from the Overdue sheets, but it doesn't unless I press enter after clicking on the formula bar. If I close the workbook and re-open it, it disappears from Overdue. Any idea how to get it to refresh automatically?

Sorry to be such a pain, I really appreciate you taking the time to help!

I'm very excited to have this cell sorted, it's going to make such a big difference!

Thank you.

Victor
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Your code in initial post is copying A:J
Rich (BB code): 
        [A1:J150].Copy Sheet3.[A1]
How do you want it?
Do you still want Col.J?
I mean just exclude Col.I from coying?
 
Upvote 0
Fuji said:
Your code in initial post is copying A:J
Rich (BB code): 
        [A1:J150].Copy Sheet3.[A1]
How do you want it?
Do you still want Col.J?
I mean just exclude Col.I from coying?
Click to expand...

Ah yes, sorry - I'm happy just to retain A1:I150, I've completely removed column J now that column I does the same thing.

Thank you again.
 
Upvote 0
I still need help, sorry.

1) If the value of 'G' on Master Priority List is less than or equal to 5 it is then copied to Overdue. If I then put a date in the 'I' column on the Master Priority List, it will copy it to Completed, delete it from the Master Priority List but it remains on Overdue. How can I get it to delete it from both sheets?

2) When I change 'F' (Due Date), it automatically changes 'G' (Days Left) which uses the formula '=F3-TODAY()' but if that value is now >5 days it should disappear from the Overdue sheets, but it doesn't unless I manually enter a number instead of using the above mentioned formula. If I close the workbook and re-open it, it disappears from Overdue. Any idea how to get it to refresh automatically?

Thank you again.
 
Upvote 0
Try replace the code in "Master Priority List" sheet code module with,
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Columns("f:g")) Is Nothing Then
        With Sheets("Overdue")
            .Rows("2:" & .Cells.SpecialCells(11).Row).Clear
        End With
        Me.AutoFilterMode = False
        [G1:G150].AutoFilter Field:=1, Criteria1:="<=5"
        [A1:J150].Copy Sheet3.[A1]
        [A1].AutoFilter
    ElseIf Not Intersect(Target, Columns("I")) Is Nothing Then
        With Sheets("Completed")
            .Rows("2:" & .Cells.SpecialCells(11).Row).Clear
        End With
        Application.EnableEvents = False
        Target.EntireRow.Copy Sheets("Completed").Range("a" & Rows.Count).End(xlUp)(2)
        Target.EntireRow.Delete
        Application.EnableEvents = True
        Run Me.CodeName & ".worksheet_change", [f1]
    End If
End Sub
 
Upvote 0
Yay - thank you, thank you, thank you - you're the best! :)

It's 99% there now - the only issue now is when it moves a completed row to the completed sheet, it copies on top of row 2, but won't add it to a new line (I want to be able to see all the completed tasks).

If you were able to help me with this, I'd really appreciate it.

Thank you again, you've really helped me out.
 
Upvote 0
OK, just delete the lines
Code: 
        With Sheets("Completed")
           .Rows("2:" &amp; .Cells.SpecialCells(11).Row).Clear
        End With
 
Upvote 0
Fuji - thank you so very much for your help and patience - I really appreciate it!!!! It does exactly what I was hoping for - you're the best!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Select the Name in the Data Validation list on a cell value then the same name run the sub macro
Replies
9
Views
440
kpmsivaprakasam2003
K
B
  • Solved
Getting Macros to run automatically
Replies
3
Views
662
mumps
mumps
J
  • Question Question
Copying Rows Based on Column Value
Replies
1
Views
147
jolivanes
jolivanes
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
338
tixarah
T
R
  • Solved
VBA moving rows
2
Replies
14
Views
1K
Joe4
Joe4

Forum statistics

Threads
1,224,521
Messages
6,179,289
Members
452,902
Latest member
Knuddeluff

Share this page

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