Automatically move row to new tab when expiry date is reached

[brodiej]

Hi everyone,

I am very new to VBA and have tried to look at previous posts, but I've ended up more confused that I was to start with! I am creating a chemical inventory list, to help us keep track of expired products. I have a separate tab for each room in which chemicals are kept, and one tab titled "waste". I would like to automatically move rows when the expiration date is met, however, I would like to only move some of the columns of each row. I have included a screenshot of one of my tabs, as well as the waste tab, where I would like the expired product to go.

So, I would like excel to move the row in tab "Stores" based on the date in K to the tab "Waste". I would like to move column C to B, G to E, H to F, I to G, J to H, and L to I (from stores to waste)


I apologise in advance for being such a beginner! Really appreciate any help, Thank you

 

[Micron]

Did you consider using a helper column to flag the expired material as expired? A simple formula in that column would fill in with whatever you want. Something like
If(K9<=Now(),"EXPIRED", "")

 

[brodiej]

Hi, yes I have already used conditional formatting to automatically highlight the cells when expired. I would like to move them to another tab once expired as it means our waste sheet will be automatically completed and we can send it straight off for collection.

 

[Micron]

Can help you help others to help you, but I think that's just about it given the scope of the work as I see it (I'm a novice at Excel vba, not so with Access vba). I suggest you post real data, either as copy/paste or better, use xl2bb tool. If people had something to work with, you might garner more responses. Pictures are often good, but they require that responders manually recreate your tabs and all the data you show. You might also be asked if there is an correlation between item number column in the 2 sheets. I'm guessing not. FWIW, I find that a bit misleading/confusing. Given the goal, item 1 in stores tab and item 1 in waste tab won't necessarily be related, but the duplicate column names suggests otherwise.

 

[brodiej]

Can help you help others to help you, but I think that's just about it given the scope of the work as I see it (I'm a novice at Excel vba, not so with Access vba). I suggest you post real data, either as copy/paste or better, use xl2bb tool. If people had something to work with, you might garner more responses. Pictures are often good, but they require that responders manually recreate your tabs and all the data you show. You might also be asked if there is an correlation between item number column in the 2 sheets. I'm guessing not. FWIW, I find that a bit misleading/confusing. Given the goal, item 1 in stores tab and item 1 in waste tab won't necessarily be related, but the duplicate column names suggests otherwise.

Hi, Okay, I will try and provide more data, thank you for the advice. I understand it is a little confusing, I did my best to explain it! The item number has no correlation between the tabs, just a way for us to quickly see how many items are in each location

 

[brodiej]

Hi, Okay, I will try and provide more data, thank you for the advice. I understand it is a little confusing, I did my best to explain it! The item number has no correlation between the tabs, just a way for us to quickly see how many items are in each location

Tab 1: Stores

Chem Inventory Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
9	Item No.	Location Within Room	ChemicalNameDescription	Company	Cat	Lot	Container Type	No. of containers	Container size(g,µl,ml,kg,l)	Physical State	Expiry	Container empty?	Hazard Properties HP1-HP15(List all applicable) SEE TAB 2
10	1	Chemical Bay	Chemical 1	Comp 1	Cat 1	Lot 1	Plastic	1	1	Powder	11/1/25	No
11	2	Chemical Bay	Chemical 2	Comp 2	Cat 2	Lot 2	Plastic	1	1	Powder	Retest 2025	No
12	3	Chemical Bay	Chemical 3	Comp 3	Cat 3	Lot 3	Plastic	1	1	Liquid	NA	No
Test

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L10:L903	Cell Value	contains "Yes"	text	NO
K10:K544	Expression	=$K10<TODAY()	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L10:L12	List	Yes, No
J10:J12	List	Solid, Liquid, Gas, Powder, Mixed
G10:G12	List	Plastic, Glass, Cardboard, Metal

Tab 2: Waste

Chem Inventory Test.xlsx
	A	B	C	D	E	F	G	H	I
9	Item No.	Chemical Name or Description of Waste	Components	Concentration	Container Type	No. of containers	Container size(g,µl,ml,kg,l)	Physical State	Hazard Properties HP1-HP15(List all applicable) SEE TAB 2
10			Required for volumes >5l
11	1
12	2
13	3
Waste

Cells with Data Validation
Cell	Allow	Criteria
H11:H13	List	Solid, Liquid, Gas, Powder, Mixed

 

[brodiej]

Tab 1: Stores

Chem Inventory Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
9	Item No.	Location Within Room	ChemicalNameDescription	Company	Cat	Lot	Container Type	No. of containers	Container size(g,µl,ml,kg,l)	Physical State	Expiry	Container empty?	Hazard Properties HP1-HP15(List all applicable) SEE TAB 2
10	1	Chemical Bay	Chemical 1	Comp 1	Cat 1	Lot 1	Plastic	1	1	Powder	11/1/25	No
11	2	Chemical Bay	Chemical 2	Comp 2	Cat 2	Lot 2	Plastic	1	1	Powder	Retest 2025	No
12	3	Chemical Bay	Chemical 3	Comp 3	Cat 3	Lot 3	Plastic	1	1	Liquid	NA	No
Test

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L10:L903	Cell Value	contains "Yes"	text	NO
K10:K544	Expression	=$K10<TODAY()	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L10:L12	List	Yes, No
J10:J12	List	Solid, Liquid, Gas, Powder, Mixed
G10:G12	List	Plastic, Glass, Cardboard, Metal

Tab 2: Waste

Chem Inventory Test.xlsx
	A	B	C	D	E	F	G	H	I
9	Item No.	Chemical Name or Description of Waste	Components	Concentration	Container Type	No. of containers	Container size(g,µl,ml,kg,l)	Physical State	Hazard Properties HP1-HP15(List all applicable) SEE TAB 2
10			Required for volumes >5l
11	1
12	2
13	3
Waste

Cells with Data Validation
Cell	Allow	Criteria
H11:H13	List	Solid, Liquid, Gas, Powder, Mixed

So, Move (from stores to waste) C to B, G to E, H to F, I to G, J to H, M to I