bit of a curly one!

[hepkat63]

Hi all,
I have a bit of a curly problem that I'm hoping someone can help me work though!
Every day, I am required to manipulate a couple of existing spreadsheets in able to consolidate data in to a certain format as so I can then copy/paste that information into SAP.
I can do this with lots of manual steps, however of course would like to automate it, but the variables have made it a confusing problem.
I have one spreadsheet called 'issues. This contains multiple columns which are populated as goods are issued from our store.
I have another spreadsheet that is created daily by downloading information from SAP. The name of this spreadsheet changes everyday with the download based on date.
First problem: How to isolate the required five columns and varying amount of rows of data from the 'issues' sheet that are between yesterdays date and todays date AND from the 2nd last row that has been highlighted in Yellow colour to the last row that is highlighted in the Yellow color (I highlight the row at the end of the shift to differentiate between shifts). Then select and copy this data to another spreadsheet
Second problem: In the downloaded spreadsheet from SAP, go to the first empty row (as each day the amount of data varies), then go to column number four and paste the data from Problem 1 (issues sheet)
Hoping someone can help!

 

[Colo]

Hi,

Can you provide us the data of the 2nd spreadsheet as XLBB?

Are those actual worksheet names? Those worksheets are in the same workbook?

Set shDLSAP = Sheets("GoodsIssuedToSap_20220829_131456.xlsx") 'Change here to suit actual sheet name
Set shIssues = Sheets("store tally report - MASTER.xlsm") 'Change here to suit actual sheet name

 

[hepkat63]

Here is the Issues sheet:

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	SAP	Qty	BLANK	SLOC	Department	Issuer	DATE	Description	Laundry ID	Name	Blank2	Supervisor Slip	Replaced/New	Time	blank3	Storage Location
2	500-220092	1		SL11	11901234	LPRIOR	26 Aug 2022	SLEEVE 50CM CUT-RESISTANT GREY CUFF 93.	79	Jev Bajalia Barabad		NO	NEW	12:14:44		DRY STORE
3	500-221078	2		SL11	10201234	NSUKWIPAD	26 Aug 2022	GLOVE POLAR BEAR RED SZ 7	55	Shengzhen Jiao		NO	NEW	12:59:09		DRY STORE
4	500-202971	1		SL11	10201234	MARK	26 Aug 2022	STEEL **** COMBI 7-3882-25-66	379	Brodie McGillivray		NO	NEW	13:38:22		KNIFE ST8
5	500-358713	1		SL11	10201234	MARK	26 Aug 2022	GLOVE SILVERLINED 88-350 SZ7	999	Gloves for Laundry		NO	NEW	13:48:54		DRY STORE
6	500-358715	2		SL11	10201234	MARK	26 Aug 2022	GLOVE SILVERLINED 88-350 SZ8	999	Gloves for Laundry		NO	NEW	13:50:14		DRY STORE
7	500-221080	1		SL11	11901234	MARK	26 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	238	Theresa Galosala		YES	NEW	14:37:12		DRY STORE
8	500-269892	1		SL11	11901234	MARK	26 Aug 2022	BOOT PU GREEN BEKINA STEPLITEX SZ 9	392	Erwin Encarnacion		YES	REPLACED	14:45:09		GUMBOOTS
9	500-207507	1		SL11	11901234	MARK	26 Aug 2022	GLASSES SAFETY ANTI FOG CLEAR MEVX2C	18	Chass Marshall		YES	NEW	14:55:29		DRY STORE
10	500-207227	1		SL11	13961234	MARK	26 Aug 2022	GLOVES FREEZER FUR LINED	472	Ismail Bin Adams		YES	REPLACED	14:56:22		DRY STORE
11	500-221080	2		SL11	11901234	MARK	26 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	428	Maikeli Waitui		YES	NEW	15:10:50		DRY STORE
12	500-221081	2		SL11	11901234	NSUKWIPAD	26 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	403	Christopher Tobin		YES	NEW	15:27:06		DRY STORE
13	500-218812	1		SL11	11901234	NSUKWIPAD	26 Aug 2022	EARMUFFS PELTOR H7P3E CLIP-ON	150	Mathew Leahy		YES	NEW	16:49:55		DRY STORE
14	500-207507	1		SL11	11901234	NSUKWIPAD	26 Aug 2022	GLASSES SAFETY ANTI FOG CLEAR MEVX2C	423	Moo Nay		YES	NEW	17:04:40		DRY STORE
15	500-358713	5		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ7	999	Gloves for Laundry		NO	NEW	04:47:20		DRY STORE
16	500-358715	12		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ8	999	Gloves for Laundry		NO	NEW	04:48:07		DRY STORE
17	500-358787	8		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ9	999	Gloves for Laundry		NO	NEW	04:48:37		DRY STORE
18	500-358789	2		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ10	999	Gloves for Laundry		NO	NEW	04:48:59		DRY STORE
19	500-207504	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	496	Suho Kang		NO	NEW	04:58:47		DRY STORE
20	500-267938	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	496	Suho Kang		NO	NEW	04:59:03
21	500-218872	2		SL11	13961234	CBIRTHIS	29 Aug 2022	GLOVES BEAVER NAVY WITH YELLOW STIPLES	484	Kaminieli Varo		NO	NEW	05:05:52		DRY STORE
22	500-218872	2		SL11	13961234	CBIRTHIS	29 Aug 2022	GLOVES BEAVER NAVY WITH YELLOW STIPLES	421	Tevita Ralumu Taga		NO	NEW	05:06:07		DRY STORE
23	500-207504	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	72	Owen Bulehite		NO	NEW	05:14:47		DRY STORE
24	500-267938	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	72	Owen Bulehite		NO	NEW	05:15:06
25	500-208308	1		SL11	10201234	CBIRTHIS	29 Aug 2022	APRON PVC 90 X 120 WHITE	254	Luke Fisher		NO	NEW	05:32:05		DRY STORE
26	500-267938	2		SL11	10201234	CBIRTHIS	29 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	142	Najabullah Rezae		NO	NEW	05:32:23
27	500-208905	20		SL11	10201234	CBIRTHIS	29 Aug 2022	CARTRIDGES GREEN STUNNER BX1000	414	Joel Dempsey		NO	NEW	05:38:14		KNIFE FLAM
28	500-267925	500		SL11	10201234	CBIRTHIS	29 Aug 2022	O RING WHITE LARGE 'D' 15MM	214	Celeste Nugas		NO	NEW	05:39:11		DRY STORE
29	500-208308	1		SL11	10201234	SROLFE	29 Aug 2022	APRON PVC 90 X 120 WHITE	92	Chunbo Ge		YES	NEW	05:42:41		DRY STORE
30	500-208308	1		SL11	10201234	SROLFE	29 Aug 2022	APRON PVC 90 X 120 WHITE	112	Yingliang (Leon) Chen		YES	NEW	05:46:22		DRY STORE
31	500-206496	1		SL11	10201234	LPRIOR	29 Aug 2022	GLOVES NITTY GRITTY (12PR BOX)	180	Emma Cassidy		NO	REPLACED	06:08:31		DRY STORE
32	500-204244	1		SL11	11901234	LPRIOR	29 Aug 2022	SQUEEGEE RED 600MM	39	Allan Epworth		NO	NEW	06:18:39		DRY STORE
33	500-264285	1		SL11	10201234	SROLFE	29 Aug 2022	COUNTER TALLY DESKTOP 2 BANK 4 DIGIT D2	1	Maintenance		NO	NEW	06:45:43		DRY STORE
34	500-221080	1		SL11	11901234	LPRIOR	29 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	95	Marde Dave O Perez		NO	REPLACED	07:46:32		DRY STORE
35	500-207507	1		SL11	13001234	LPRIOR	29 Aug 2022	GLASSES SAFETY ANTI FOG CLEAR MEVX2C	452	Avish Malik		NO	NEW	08:13:12		DRY STORE
36	500-218872	2		SL11	13961234	LPRIOR	29 Aug 2022	GLOVES BEAVER NAVY WITH YELLOW STIPLES	449	Shaun Lyons		YES	NEW	08:44:28		DRY STORE
37	500-207134	1		SL11	13961234	LPRIOR	29 Aug 2022	GLOVES COTTON KNITTED	449	Shaun Lyons		YES	NEW	08:44:46		DRY STORE
38	500-208308	1		SL11	13001234	LPRIOR	29 Aug 2022	APRON PVC 90 X 120 WHITE	471	Phineas Samu		NO	NEW	09:33:51		DRY STORE
39	500-229077	2		SL11	13001234	LPRIOR	29 Aug 2022	GLOVE GREEN PVC DOUBLE DIP 45CM	471	Phineas Samu		NO	NEW	09:34:12		DRY STORE
40	500-221078	2		SL11	10201234	NSUKWIPAD	29 Aug 2022	GLOVE POLAR BEAR RED SZ 7	378	Jerry Francis		NO	NEW	13:11:37		DRY STORE
41	500-207808	1		SL11	10201234	NSUKWIPAD	29 Aug 2022	BOOT PU GREEN BEKINA STEPLITEX SZ 10	350	Jimson Sale		NO	NEW	13:20:50		GUMBOOTS
42	500-221081	2		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	150	Mathew Leahy		YES	NEW	14:27:22		DRY STORE
43	500-221081	1		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	173	Leonard Lyons		YES	NEW	14:29:32		DRY STORE
44	500-221080	2		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	412	Manasa Ravoki		YES	NEW	14:42:23		DRY STORE
45	500-221079	1		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR YELLOW SZ 8	403	Christopher Tobin		YES	NEW	14:58:55		DRY STORE
46	500-207227	1		SL11	11901234	MARK	29 Aug 2022	GLOVES FREEZER FUR LINED	146	Carl Patenia		YES	NEW	17:26:12		DRY STORE
47	500-221080	2		SL11	11901234	NSUKWIPAD	29 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	388	Mohammad Hassan Mohammad Hassan		YES	NEW	19:29:32		DRY STORE
48	500-358713	7		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ7	999	Gloves for Laundry		NO	NEW	04:42:17		DRY STORE
49	500-358715	13		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ8	999	Gloves for Laundry		NO	NEW	04:42:39		DRY STORE
50	500-358787	2		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ9	999	Gloves for Laundry		NO	NEW	04:42:58		DRY STORE
51	500-358789	2		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ10	999	Gloves for Laundry		NO	NEW	04:43:25		DRY STORE
52	500-221080	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	362	Aukusition (Tino) Simeti		NO	NEW	05:07:40		DRY STORE
53	500-207504	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	496	Suho Kang		NO	NEW	05:08:07		DRY STORE
54	500-267938	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	496	Suho Kang		NO	NEW	05:08:24
55	500-221081	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	173	Leonard Lyons		NO	NEW	05:11:10		DRY STORE
56	500-207504	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	72	Owen Bulehite		NO	NEW	05:12:49		DRY STORE
57	500-267938	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	72	Owen Bulehite		NO	NEW	05:13:03
58	500-208306	1		SL11	11901234	CBIRTHIS	30 Aug 2022	APRON PVC 90 X 120 RED	407	Junior Bata		NO	NEW	05:28:14		DRY STORE
59	500-221081	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	300	Callum Prior		NO	NEW	05:29:45		DRY STORE
60	500-206015	1		SL11	10201234	CBIRTHIS	30 Aug 2022	STEEL F **** 12IN COMBI SQUARE 7-5982-30	92	Chunbo Ge		NO	NEW	05:40:26
61	500-207507	1		SL11	10201234	CBIRTHIS	30 Aug 2022	GLASSES SAFETY ANTI FOG CLEAR MEVX2C	92	Chunbo Ge		YES	NEW	05:44:56		DRY STORE
62	500-218872	2		SL11	13961234	LPRIOR	30 Aug 2022	GLOVES BEAVER NAVY WITH YELLOW STIPLES	202	Rodney Price Bonunga		YES	NEW	05:58:30		DRY STORE
63	500-207134	1			13961234	LPRIOR	30 Aug 2022	GLOVES COTTON KNITTED	202	Rodney Price Bonunga		YES	NEW	05:58:57		DRY STORE
64	500-218812	1		SL11	11901234	LPRIOR	30 Aug 2022	EARMUFFS PELTOR H7P3E CLIP-ON	35	Richard Marks		NO	NEW	06:35:53		DRY STORE
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:P64	Cell Value	contains "KNIFE"	text	NO
P2:SP64	Cell Value	contains "BOOTS"	text	NO
N2:O64	Cell Value	contains "KNIFE CUPB"	text	NO

Hi,

Can you provide us the data of the 2nd spreadsheet as XLBB?

Are those actual worksheet names? Those worksheets are in the same workbook?

Set shDLSAP = Sheets("GoodsIssuedToSap_20220829_131456.xlsx") 'Change here to suit actual sheet name
Set shIssues = Sheets("store tally report - MASTER.xlsm") 'Change here to suit actual sheet name

The Issues sheet is one workbook called "store tally report - MASTER.xlsm The downloaded spreadsheet is another workbook called "goodsissuetosap ... then the date changes, so two different workbooks. Here below, I copied a section of the store tally report - MASTER.xlsm into another spreadsheet to produce a XLbb:

 

[hepkat63]

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	SAP	Qty	BLANK	SLOC	Department	Issuer	DATE	Description	Laundry ID	Name	Blank2	Supervisor Slip	Replaced/New	Time	blank3	Storage Location
2	500-220092	1		SL11	11901234	LPRIOR	26 Aug 2022	SLEEVE 50CM CUT-RESISTANT GREY CUFF 93.	79	Jev Bajalia Barabad		NO	NEW	12:14:44		DRY STORE
3	500-221078	2		SL11	10201234	NSUKWIPAD	26 Aug 2022	GLOVE POLAR BEAR RED SZ 7	55	Shengzhen Jiao		NO	NEW	12:59:09		DRY STORE
4	500-202971	1		SL11	10201234	MARK	26 Aug 2022	STEEL **** COMBI 7-3882-25-66	379	Brodie McGillivray		NO	NEW	13:38:22		KNIFE ST8
5	500-358713	1		SL11	10201234	MARK	26 Aug 2022	GLOVE SILVERLINED 88-350 SZ7	999	Gloves for Laundry		NO	NEW	13:48:54		DRY STORE
6	500-358715	2		SL11	10201234	MARK	26 Aug 2022	GLOVE SILVERLINED 88-350 SZ8	999	Gloves for Laundry		NO	NEW	13:50:14		DRY STORE
7	500-221080	1		SL11	11901234	MARK	26 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	238	Theresa Galosala		YES	NEW	14:37:12		DRY STORE
8	500-269892	1		SL11	11901234	MARK	26 Aug 2022	BOOT PU GREEN BEKINA STEPLITEX SZ 9	392	Erwin Encarnacion		YES	REPLACED	14:45:09		GUMBOOTS
9	500-207507	1		SL11	11901234	MARK	26 Aug 2022	GLASSES SAFETY ANTI FOG CLEAR MEVX2C	18	Chass Marshall		YES	NEW	14:55:29		DRY STORE
10	500-207227	1		SL11	13961234	MARK	26 Aug 2022	GLOVES FREEZER FUR LINED	472	Ismail Bin Adams		YES	REPLACED	14:56:22		DRY STORE
11	500-221080	2		SL11	11901234	MARK	26 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	428	Maikeli Waitui		YES	NEW	15:10:50		DRY STORE
12	500-221081	2		SL11	11901234	NSUKWIPAD	26 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	403	Christopher Tobin		YES	NEW	15:27:06		DRY STORE
13	500-218812	1		SL11	11901234	NSUKWIPAD	26 Aug 2022	EARMUFFS PELTOR H7P3E CLIP-ON	150	Mathew Leahy		YES	NEW	16:49:55		DRY STORE
14	500-207507	1		SL11	11901234	NSUKWIPAD	26 Aug 2022	GLASSES SAFETY ANTI FOG CLEAR MEVX2C	423	Moo Nay		YES	NEW	17:04:40		DRY STORE
15	500-358713	5		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ7	999	Gloves for Laundry		NO	NEW	04:47:20		DRY STORE
16	500-358715	12		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ8	999	Gloves for Laundry		NO	NEW	04:48:07		DRY STORE
17	500-358787	8		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ9	999	Gloves for Laundry		NO	NEW	04:48:37		DRY STORE
18	500-358789	2		SL11	20721234	CBIRTHIS	29 Aug 2022	GLOVE SILVERLINED 88-350 SZ10	999	Gloves for Laundry		NO	NEW	04:48:59		DRY STORE
19	500-207504	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	496	Suho Kang		NO	NEW	04:58:47		DRY STORE
20	500-267938	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	496	Suho Kang		NO	NEW	04:59:03
21	500-218872	2		SL11	13961234	CBIRTHIS	29 Aug 2022	GLOVES BEAVER NAVY WITH YELLOW STIPLES	484	Kaminieli Varo		NO	NEW	05:05:52		DRY STORE
22	500-218872	2		SL11	13961234	CBIRTHIS	29 Aug 2022	GLOVES BEAVER NAVY WITH YELLOW STIPLES	421	Tevita Ralumu Taga		NO	NEW	05:06:07		DRY STORE
23	500-207504	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	72	Owen Bulehite		NO	NEW	05:14:47		DRY STORE
24	500-267938	2		SL11	11901234	CBIRTHIS	29 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	72	Owen Bulehite		NO	NEW	05:15:06
25	500-208308	1		SL11	10201234	CBIRTHIS	29 Aug 2022	APRON PVC 90 X 120 WHITE	254	Luke Fisher		NO	NEW	05:32:05		DRY STORE
26	500-267938	2		SL11	10201234	CBIRTHIS	29 Aug 2022	GLOVE NITRILE G/T TOUCH BLUE SZ L	142	Najabullah Rezae		NO	NEW	05:32:23
27	500-208905	20		SL11	10201234	CBIRTHIS	29 Aug 2022	CARTRIDGES GREEN STUNNER BX1000	414	Joel Dempsey		NO	NEW	05:38:14		KNIFE FLAM
28	500-267925	500		SL11	10201234	CBIRTHIS	29 Aug 2022	O RING WHITE LARGE 'D' 15MM	214	Celeste Nugas		NO	NEW	05:39:11		DRY STORE
29	500-208308	1		SL11	10201234	SROLFE	29 Aug 2022	APRON PVC 90 X 120 WHITE	92	Chunbo Ge		YES	NEW	05:42:41		DRY STORE
30	500-208308	1		SL11	10201234	SROLFE	29 Aug 2022	APRON PVC 90 X 120 WHITE	112	Yingliang (Leon) Chen		YES	NEW	05:46:22		DRY STORE
31	500-206496	1		SL11	10201234	LPRIOR	29 Aug 2022	GLOVES NITTY GRITTY (12PR BOX)	180	Emma Cassidy		NO	REPLACED	06:08:31		DRY STORE
32	500-204244	1		SL11	11901234	LPRIOR	29 Aug 2022	SQUEEGEE RED 600MM	39	Allan Epworth		NO	NEW	06:18:39		DRY STORE
33	500-264285	1		SL11	10201234	SROLFE	29 Aug 2022	COUNTER TALLY DESKTOP 2 BANK 4 DIGIT D2	1	Maintenance		NO	NEW	06:45:43		DRY STORE
34	500-221080	1		SL11	11901234	LPRIOR	29 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	95	Marde Dave O Perez		NO	REPLACED	07:46:32		DRY STORE
35	500-207507	1		SL11	13001234	LPRIOR	29 Aug 2022	GLASSES SAFETY ANTI FOG CLEAR MEVX2C	452	Avish Malik		NO	NEW	08:13:12		DRY STORE
36	500-218872	2		SL11	13961234	LPRIOR	29 Aug 2022	GLOVES BEAVER NAVY WITH YELLOW STIPLES	449	Shaun Lyons		YES	NEW	08:44:28		DRY STORE
37	500-207134	1		SL11	13961234	LPRIOR	29 Aug 2022	GLOVES COTTON KNITTED	449	Shaun Lyons		YES	NEW	08:44:46		DRY STORE
38	500-208308	1		SL11	13001234	LPRIOR	29 Aug 2022	APRON PVC 90 X 120 WHITE	471	Phineas Samu		NO	NEW	09:33:51		DRY STORE
39	500-229077	2		SL11	13001234	LPRIOR	29 Aug 2022	GLOVE GREEN PVC DOUBLE DIP 45CM	471	Phineas Samu		NO	NEW	09:34:12		DRY STORE
40	500-221078	2		SL11	10201234	NSUKWIPAD	29 Aug 2022	GLOVE POLAR BEAR RED SZ 7	378	Jerry Francis		NO	NEW	13:11:37		DRY STORE
41	500-207808	1		SL11	10201234	NSUKWIPAD	29 Aug 2022	BOOT PU GREEN BEKINA STEPLITEX SZ 10	350	Jimson Sale		NO	NEW	13:20:50		GUMBOOTS
42	500-221081	2		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	150	Mathew Leahy		YES	NEW	14:27:22		DRY STORE
43	500-221081	1		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR BLACK SZ 10	173	Leonard Lyons		YES	NEW	14:29:32		DRY STORE
44	500-221080	2		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	412	Manasa Ravoki		YES	NEW	14:42:23		DRY STORE
45	500-221079	1		SL11	11901234	MARK	29 Aug 2022	GLOVE POLAR BEAR YELLOW SZ 8	403	Christopher Tobin		YES	NEW	14:58:55		DRY STORE
46	500-207227	1		SL11	11901234	MARK	29 Aug 2022	GLOVES FREEZER FUR LINED	146	Carl Patenia		YES	NEW	17:26:12		DRY STORE
47	500-221080	2		SL11	11901234	NSUKWIPAD	29 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	388	Mohammad Hassan Mohammad Hassan		YES	NEW	19:29:32		DRY STORE
48	500-358713	7		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ7	999	Gloves for Laundry		NO	NEW	04:42:17		DRY STORE
49	500-358715	13		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ8	999	Gloves for Laundry		NO	NEW	04:42:39		DRY STORE
50	500-358787	2		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ9	999	Gloves for Laundry		NO	NEW	04:42:58		DRY STORE
51	500-358789	2		SL11	20721234	CBIRTHIS	30 Aug 2022	GLOVE SILVERLINED 88-350 SZ10	999	Gloves for Laundry		NO	NEW	04:43:25		DRY STORE
52	500-221080	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE POLAR BEAR DARK BLUE SZ 9	362	Aukusition (Tino) Simeti		NO	NEW	05:07:40		DRY STORE
53	500-207504	2		SL11	11901234	CBIRTHIS	30 Aug 2022	GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	496	Suho Kang		NO	NEW	05:08:07		DRY STORE
Sheet1

Cell Formulas
Range	Formula
D2:D53	D2	=IF(ISBLANK(A2),"",IF(ISNUMBER(SEARCH("500",A2)),"SL11","SL02"))
H2:H53	H2	=IF(ISBLANK(A2),"",VLOOKUP(A2,'[store tally report - MASTER.xlsm]Product Lookup List'!$A:$D,3,FALSE))
J2	J2	=IF(ISBLANK(#REF!),"",VLOOKUP($I2,'[store tally report - MASTER.xlsm]Laundry List'!$A$1:$B$462,2,FALSE))
J3:J53	J3	=IF(ISBLANK($I2),"",VLOOKUP($I3,'[store tally report - MASTER.xlsm]Laundry List'!$A$1:$B$462,2,FALSE))
N2:N53	N2	=IF(ISBLANK($G2),"",$G2-INT($G2))
P2:P53	P2	=IF(ISBLANK(A2),"",VLOOKUP(A2,'[store tally report - MASTER.xlsm]Product Lookup List'!$A:$D,4,FALSE))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:P53	Cell Value	contains "KNIFE"	text	NO
P2:P53	Cell Value	contains "BOOTS"	text	NO
N2:O53	Cell Value	contains "KNIFE CUPB"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:D53	List	='[store tally report - MASTER.xlsm]Validation'!#REF!
K2:L53	List	='[store tally report - MASTER.xlsm]Data Validation'!#REF!
M2:M53	List	='[store tally report - MASTER.xlsm]Data Validation'!#REF!

 

[hepkat63]

so, I need to copy columns A through to H and from YELLOW highlighted to YELLOW highlighted each day from store tally report - MASTER.xlsm and then paste special (value) to the next empty line in Goodsissuedtosap spreadsheet starting at Column D so that the SAP numbers follow on.

 

[Colo]

I have organized the conditions presented to us and set the assumptions as follows If my understanding is wrong at this point, the code will not work, so please check these preconditions first.

Workbook#0 - This workbook which has a program code.
Workbook#1 - Titled "MASTER.xlsm" which has a worksheet titled "Issues".
Workbook#2 - Titles "goodsissuetosap" which has one worksheet. The name of the worksheet was created based on the date.

Place the following code on Workbook#0 and then run the code.
So the program would ask you to select Workbook#1 and Workbook#2, so please select workbooks.
Seems no data was generated on Saturdays and Sundays, so I set the filtered term for 2 business days based on yesterday.

Sub Sample2()
    Dim shDLSAP As Worksheet    'SAP worksheet
    Dim shIssues As Worksheet    'Issues worksheet
    Dim shTemp As Worksheet    'Temporary helper worksheet
    Dim dtCnt As Long
    Dim stDate As Date, endDate As Date

    Dim OpenFileName As String

    OpenFileName = Application.GetOpenFilename("Excel Files,*.xls?", , "Open Issue workbook")
    If OpenFileName <> "False" Then
        Workbooks.Open OpenFileName
        Set shIssues = Sheets("issues")    'Change here to suit actual sheet name
    End If

    OpenFileName = Application.GetOpenFilename("Excel Files,*.xls?", , "Open goodsissuetosap workbook")
    If OpenFileName <> "False" Then
        Workbooks.Open OpenFileName
        Set shDLSAP = Sheets(1)    'Assume there is only 1 worksheet has been generated
    End If

    Set shTemp = Sheets.Add    'Making a temporary helper wks

    'Filter by the date between Yesterday and Today
    shIssues.Activate
    shIssues.AutoFilterMode = False

    'Seems no data on Saturdays and Sundays
    Select Case Weekday(Date)
    Case 1    'If today is Sunday
        endDate = Date - 2
        stDate = endDate - 1
    Case 2    'If today is Monday
        endDate = Date - 3
        stDate = endDate - 1
    Case 3    'If today is Tuesday
        endDate = Date - 1
        stDate = endDate - 3
    Case 4, 5, 6, 7    'If today is Wednesday to Saturday
        endDate = Date - 1
        stDate = endDate - 1
    End Select

    'Filter data
    With shIssues.UsedRange
        .AutoFilter Field:=7, Criteria1:=Format(stDate, Range("G2").NumberFormatLocal), Operator:=xlOr, Criteria2:=Format(endDate, Range("G2").NumberFormatLocal)
        .Range(.Range("A2"), .Cells(Rows.Count, "G").End(xlUp)).Copy shTemp.Range("A1")
    End With
    'Counting fildtered data
    dtCnt = shIssues.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    shIssues.AutoFilterMode = False

    'Data transfer
    With shDLSAP.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Offset(, 2).Resize(dtCnt).Value = shTemp.Range("G1").Resize(dtCnt).Value
        .Offset(, 3).Resize(dtCnt).Value = shTemp.Range("A1").Resize(dtCnt).Value
        .Offset(, 4).Resize(dtCnt).Value = shTemp.Range("B1").Resize(dtCnt).Value
        .Offset(, 6).Resize(dtCnt).Value = shTemp.Range("C1").Resize(dtCnt).Value
        .Offset(, 7).Resize(dtCnt).Value = shTemp.Range("D1").Resize(dtCnt).Value
        .Offset(, 8).Resize(dtCnt).Value = shTemp.Range("E1").Resize(dtCnt).Value
    End With

    'Deleting the helper worksheet
    Application.DisplayAlerts = False
    shTemp.Delete
    Application.DisplayAlerts = True

    'Closing the workbook which has Issues sheet
    shIssues.Parent.Close False
    Application.ScreenUpdating = True
End Sub

 

[hepkat63]

thank you. So, I can't get this to work either though. Sorry, I'm clearly not giving you the information you need!
I'll try again.
So, the Issues Workbook is called "store tally report - MASTER.xlsm" It has six worksheets in it, but the information I require is on the first worksheet only - which is simply called "1"
The procedure I currently manually perform, is that I go to the Computer where this issues workbook is. I find the last issue entry, highlight the line in YELLOW, then save the workbook.
I then download the workbook from SAP and it saves it locally as "goodsissedtosap******" and puts a timestamp date - so filename is never the same.
So, now I have two workbooks open - the "store tally report - Master.xlsm"(it always opens read-only as it is constantly open on another computer) and the "goodsissuedtoSAPxxxx".
I then highlight from column A to G on the first line under the last yellow highlighted row (which is yesterdays date) down to the yellow highlighted row from today and COPY. So, I have maybe 30 rows of information, seven columns wide.
With that information on the clipboard, I got to the goodsissuedtosap workbook, click in column d under the last line of data and PASTE the information in (so that the SAP numbers line up).
I hope that helps?

 

[hepkat63]

I've uploaded a screen recording of what I do - it's not the best job of screen recording (and apologies for the music playing too ), but you'll see what I mean I think: bandicam 2022-08-31 12-10-55-689.mp4

 

[hepkat63]

did you require any more information please?

 

[Colo]

Hi,

Still no time to check those what you provided. For a while I'm going to be busy, so I'll have a look when I have time. Hope other members also helps.

 

[hepkat63]

ok. thank you.