VBA Help Please

[victorski]

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
	A	B	C	D	E	F	G	H	I
1	Priority	Task	Comments	ASSIGNED TO	PROGRESS	DUE	DAYS LEFT	START	END
2
3	1				10%	31/12/2024	33
4	1.1				0%	15/12/2024	17
5	1				10%	31/12/2024	33
6	1.2				0%	15/12/2024	17
7	1.2				0%	20/01/2025	53
8	1.2				0%	20/01/2025	53
9	1.2				0%	15/12/2024	17
10	1.3				0%	20/12/2024	22
11	1.4				0%	31/12/2025	398
12	1.4				0%	15/12/2024	17
13	1.4				0%	20/01/2025	53
14	1.4				0%	20/01/2025	53
15	1.4				0%	15/12/2024	17
16	1.4				0%	1/03/2025	93
17	1.5				5%	31/12/2024	33
18	1.5				0%	15/12/2024	17
19	1.5				0%	20/01/2025	53
20	1.5				0%	20/01/2025	53
21	1.5				95%	15/12/2024	17
22	1.5				0%	15/11/2024	-13
23	1.5				0%	15/11/2024	-13
24	1.5				20%	6/11/2024	-22
25	1.5				10%	30/10/2024	-29
26	1.5				0%	23/11/2024	-5
27
28	2.1				0%	31/12/2024	33
29	2.1				0%	7/11/2024	-21
30	2.1				0%	20/01/2025	53
31	2.2				0%	31/12/2027	1128
32	2.3				0%	31/12/2027	1128
33	2.4				0%	31/12/2024	33
34	2.5				0%	31/12/2027	1128
35
36	3.1				0%	12/03/2025	104
37	3.2				0%	11/02/2025	75
38	3.3				0%	3/03/2025	95
39
40	4.1				0%	3/12/2024	5
41	4.1				0%	8/10/2024	-51
42	4.1				0%	8/11/2024	-20
43	4.2				0%
44	4.3				0%
45	4.4				0%	3/12/2024	5
46	4.4				0%	8/10/2024	-51
47	4.4				0%	8/11/2024	-20
48	4.5				0%	14/01/2025	47
49	4.6				0%
50	5
51	5.1				100%			24/07/2024	24/07/2024
52	5.2				0%	3/03/2025	95
53	5.2				95%	15/10/2024	-44	15/08/2024	24/10/2024
54	5.2				0%
55	5.2				0%	31/10/2024	-28
56	5.2				0%	15/12/2024	17
57	5.2				90%	31/10/2024	-28
58	5.2				0%	5/12/2024	7
59	5.2				0%	5/12/2024	7
60	5.2				0%	10/12/2024	12
61	5.2				0%	20/01/2025	53
62	5.3				0%	3/03/2025	95
63	6
64	6.1				90%	4/12/2024	6
Priority Master List

Cell Formulas
Range	Formula
G3:G26,G64,G55:G62,G52:G53,G45:G48,G40:G42,G36:G38,G28:G34	G3	=F3-TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5	Expression	=AND($G5<>"",$G5<=5)	text	NO
I3	Expression	=AND($G3<>"",$G3<=5)	text	NO
I10	Expression	=AND($G10<>"",$G10<=5)	text	NO
I4	Expression	=AND($G4<>"",$G4<=5)	text	NO
I6:I9	Expression	=AND($G6<>"",$G6<=5)	text	NO
E64	Other Type	DataBar		NO
E64	Expression	=AND($G64<>"",$G64<=5)	text	NO
E28	Other Type	DataBar		NO
E31:E34	Other Type	DataBar		NO
E55:E56	Other Type	DataBar		NO
E57:E62,E51:E54	Other Type	DataBar		NO
E29:E30	Other Type	DataBar		NO
D67:I154,D14:I63,D3:H13,I11:I13	Expression	=AND($G3<>"",$G3<=5)	text	NO
E18:E20	Other Type	DataBar		NO
E21:E26,D63,E35:E49,E3:E17,E54	Other Type	DataBar		NO

Overdue Mini-sheet:

IDLMP Works Schedule EXTRA TEST.xlsm
	A	B	C	D	E	F	G
1	Priority	Task	Comments	ASSIGNED TO	PROGRESS	DUE	DAYS LEFT
2	1.5				0%	15/11/2024	-13
3	1.5				0%	15/11/2024	-13
4	1.5				20%	6/11/2024	-22
5	1.5				10%	30/10/2024	-29
6	1.5				0%	23/11/2024	-5
7	2.1				0%	7/11/2024	-21
8	4.1				0%	3/12/2024	5
9	4.1				0%	8/10/2024	-51
10	4.1				0%	8/11/2024	-20
11	4.4				0%	3/12/2024	5
12	4.4				0%	8/10/2024	-51
13	4.4				0%	8/11/2024	-20
14	5				95%	15/10/2024	-44
15	5				0%	31/10/2024	-28
16	5				90%	31/10/2024	-28
Overdue

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E15	Other Type	DataBar		NO
E14,E16	Other Type	DataBar		NO
E7	Other Type	DataBar		NO
D19:G104,D2:G16	Expression	=AND($G2<>"",$G2<=5)	text	NO
E8:E13,E2:E6	Other Type	DataBar		NO

Completed Mini-sheet:

IDLMP Works Schedule EXTRA TEST.xlsm
	A	B	C	D	E	F	G	H	I
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

 

[hicksi]

Just an FYI...

You use "msht.Rows.End(xlDown).Row"
to get to the last row of your data.

I got caught out by this a few times, so instead, I now ALWAYS use:
msht.Rows(Rows.Count).End(xlUp).Row.

Your formula gets the wrong information if there's a blank row.
It can also lead to truly slow processing if there is only one row to copy (xlDown goes to row 10,000,000-odd)

 

[hicksi]

I think that I messed this up... It appears that it works using column A, and if that's blank it fails.

The last row (which MAY be a blank row, but at least it's past the last one) is:

msht.Range("A1").SpecialCells(xlLastCell).Row