VBA to Capture Cell Value

[jarett]

Trying to write a VBA script to capture the value in cell J2 each time we open the file, I would want to record the value in J2 in the rows below and maybe also record the date in the "I" column next to that value. Each time the file is opened it refreshes. I only got to the very first part of the VBA code, haven't found anything else on the web to help finish it off if its possible.

Private Sub Workbook_Open()
Range("J3") = Range("J2").Value

End Sub

Order_Line_Fill_Rate.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ItemCode	ItemCodeDesc	WarehouseCode	QuantityOrdered	QuantityBackordered	UDF_QTY_PULLED	UDF_INVENTORIED_ITEM	Total Lines	BO Lines	Line Fill %
2	1332L	1332L TIG WELDING GLOVE CUT RE	000	48	0	0	Y	1498	330	78%
3	1332L	1332L TIG WELDING GLOVE CUT RE	000	84	0	84	Y
4	10019063-GRAY-XLR	MNS FR SLD VENT LS WRK SHRT SI	000	5	0	5	Y
5	CDKW038-NAVYBLUE	20 oz. Viking Nova Tumbler	000	48	48	0	Y
6	10022599-GRAY-LGR	MNS FR AIR HENLEY LS TOP SILVR	000	3	0	3	Y
7	10012552-BLUE-3230	MNS FR M4 RLX BSC BOOT CUT JEA	000	3	0	3	Y
8	10012552-BLUE-4032	MNS FR M4 RLX BSC BOOT CUT JEA	000	5	0	5	Y
9	10019063-GRAY-XXLR	MNS FR SLD VENT LS WRK SHRT SI	000	5	0	5	Y
10	SHE01-5000-ICE GREY-3XL	Gildan Heavy Cotton T-Shirt LE	000	5	0	5	Y
11	FQD810-10	QUATRO DIELECTRIC REGULAR BOOT	000	1	0	1	Y
12	SHENHK01-G2XL-V3XL-R3XL	SHELTON NEW HIRE KIT GLOVE 2XL	000	1	0	1	Y
13	FR44-YELLOW-3XL	Sealtex Flame FR Hi-Vis 50in C	000	1	0	1	Y
Order_Fill_Rate

Cell Formulas
Range	Formula
H2	H2	=COUNTA(A:A)
I2	I2	=COUNTIF(E:E,"> 0")
J2	J2	=ABS((I2-H2)/H2)

Named Ranges
Name	Refers To	Cells
ExternalData_1	=Order_Fill_Rate!$A$1:$G$1498	H2

 

[lrobbo314]

How about this?

Private Sub Workbook_Open()
Dim LR As Long:     LR = Range("J" & Rows.Count).End(xlUp).Row + 1

Range("J" & LR) = Range("J2").Value
Range("I" & LR) = Now()
End Sub

 

[jarett]

Question, the code worked and populated the row below my values, however when I just opened it again nothing happened? Will this only work once a day? Or do I have to run the VBA each time?

Order_Line_Fill_Rate.xlsm
	H	I	J
1	Total Lines	BO Lines	Line Fill %
2	1602	346	78%
3		8/2/2023 13:09	78%
4
5
6
Order_Fill_Rate

Cell Formulas
Range	Formula
H2	H2	=COUNTA(A:A)
I2	I2	=COUNTIF(E:E,"> 0")
J2	J2	=ABS((I2-H2)/H2)

Named Ranges
Name	Refers To	Cells
ExternalData_1	=Order_Fill_Rate!$A$1:$G$1602	H2

 

[lrobbo314]

No, it should work every time. I tested it a couple of times this morning before I sent the code. Seemed to be working as expected.

Record on Open.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ItemCode	ItemCodeDesc	WarehouseCode	QuantityOrdered	QuantityBackordered	UDF_QTY_PULLED	UDF_INVENTORIED_ITEM	Total Lines	BO Lines	Line Fill %
2	1332L	1332L TIG WELDING GLOVE CUT RE	0	48	0	0	Y	13	1	0.923077
3	1332L	1332L TIG WELDING GLOVE CUT RE	0	84	0	84	Y		8/2/2023 9:56	0.923077
4	10019063-GRAY-XLR	MNS FR SLD VENT LS WRK SHRT SI	0	5	0	5	Y		8/2/2023 9:56	0.923077
5	CDKW038-NAVYBLUE	20 oz. Viking Nova Tumbler	0	48	48	0	Y
6	10022599-GRAY-LGR	MNS FR AIR HENLEY LS TOP SILVR	0	3	0	3	Y
Sheet1

Off the top of my head, I can't think of a reason it wouldn't fire each time you open the workbook.

 

[jarett]

No, it should work every time. I tested it a couple of times this morning before I sent the code. Seemed to be working as expected.

Record on Open.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ItemCode	ItemCodeDesc	WarehouseCode	QuantityOrdered	QuantityBackordered	UDF_QTY_PULLED	UDF_INVENTORIED_ITEM	Total Lines	BO Lines	Line Fill %
2	1332L	1332L TIG WELDING GLOVE CUT RE	0	48	0	0	Y	13	1	0.923077
3	1332L	1332L TIG WELDING GLOVE CUT RE	0	84	0	84	Y		8/2/2023 9:56	0.923077
4	10019063-GRAY-XLR	MNS FR SLD VENT LS WRK SHRT SI	0	5	0	5	Y		8/2/2023 9:56	0.923077
5	CDKW038-NAVYBLUE	20 oz. Viking Nova Tumbler	0	48	48	0	Y
6	10022599-GRAY-LGR	MNS FR AIR HENLEY LS TOP SILVR	0	3	0	3	Y
Sheet1

Off the top of my head, I can't think of a reason it wouldn't fire each time you open the workbook.

Hmm, when I open the workbook it runs a background query that I have pulling data from a SQL connection, could that be why? I've closed and opened it after a minute and waited 2 hours and I don't think the VBA ran except the first time I opened it.

 

[lrobbo314]

Does it work if you manually run it? I.E. open the VBE and hit F5.

 

[Joe4]

Are you sure that you are saving the file before closing each time to make sure those updates are saved to the file?

 

[jarett]

OK, ran the VBA manually and it updated the line to this;

Order_Line_Fill_Rate.xlsm
	H	I	J	K
1	Total Lines	BO Lines	Line Fill %
2	1627	347	79%
3		8/2/2023 13:09	78%
4		8/2/2023 16:15	79%
5
Order_Fill_Rate

Cell Formulas
Range	Formula
H2	H2	=COUNTA(A:A)
I2	I2	=COUNTIF(E:E,"> 0")
J2	J2	=ABS((I2-H2)/H2)

Named Ranges
Name	Refers To	Cells
ExternalData_1	=Order_Fill_Rate!$A$1:$G$1627	H2

Saved and closed the file, then reopened it and nothing happened. Opened the file again and opened VBA to manually run it and it worked.

Order_Line_Fill_Rate.xlsm
	H	I	J
1	Total Lines	BO Lines	Line Fill %
2	1627	347	79%
3		8/2/2023 13:09	78%
4		8/2/2023 16:15	79%
5		8/2/2023 16:18	79%
6
7
Order_Fill_Rate

Cell Formulas
Range	Formula
H2	H2	=COUNTA(A:A)
I2	I2	=COUNTIF(E:E,"> 0")
J2	J2	=ABS((I2-H2)/H2)

Named Ranges
Name	Refers To	Cells
ExternalData_1	=Order_Fill_Rate!$A$1:$G$1627	H2

The only thing thing I could see different is I am saving it under a "One Drive" folder, would that do something?

 

[Joe4]

The only thing thing I could see different is I am saving it under a "One Drive" folder, would that do something?

That is a very important detail!
See here:

https://www.reddit.com/r/excel/comments/112ahmw

 

[jarett]

That is a very important detail!
See here:

https://www.reddit.com/r/excel/comments/112ahmw

Well, at least we know the reason now, just trying to figure out if I can fix or have to get IT involved. Cant fix via the link you sent didn't work, tried moving the file to a regular folder on our physical server and then went into trust setting to enable all macros, VBA runs manually but still doesn't run when you open the file.
Getting IT involved now I guess.