create a new sheet & save as pdf and add hyperlink & open

[leap out]

hi experts

I have two sheets the first is INV contains source data and the second should create based on current year is "2021 "
so I want save sheet INV as pdf to this directory "D:\process\inventory\2021" the folder 2021 contains many subfolders JAN,FEB .. up to DEC then should save the file as pdf to subfolder based on current month for instance current month is MAY then should save file in subfolder name's MAY and I would create a new sheet name's 2021 based on current year and when save file should copy the name file in sheet 2021 under the month relating of it as in topic header based on current month and add hyperlink to open the file
note : the file name should like this the text "INVENTORY OIL OF DATE " & DATE(today) then should like this "INVENTORY OIL OF DATE 05/22/2021 .PDF" and when finish this year 2021 should create a new year based on current year 2022 and so on

MONTHS.xlsm
	A	B	C	D	E
1	ITEM	BRAND	TYPE	MONAFACTURE	Q1
2	1	10W40 12x1L	CAS	SU	567
3	2	10W40 12x1L	ENI	IT	54
4	3	10W40 12x1L	Q8	EU	1234
5	4	10W40 208L	CAS	SU	234
6	5	10W40 208L	ENI	IT	123
7	6	10W40 208L	Q8	EU	132
8	7	10W40 4x4L	CAS	SU	45
9	8	10W40 4x4L	ENI	IT	67
10	9	10W40 4x4L	Q8	EU	123
11	10	20W50 4x4L	CAS	SU	87
12	11	20W50 4x4L	ENI	IT	98
INV1

MONTHS.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
2	INVENTORY OIL OF DATE 01/01/2021 .PDF	INVENTORY OIL OF DATE 02/01/2021 .PDF	INVENTORY OIL OF DATE 03/01/2021 .PDF
3	INVENTORY OIL OF DATE 01/02/2021 .PDF	INVENTORY OIL OF DATE 02/02/2021 .PDF	INVENTORY OIL OF DATE 03/02/2021 .PDF
4	INVENTORY OIL OF DATE 01/03/2021 .PDF	INVENTORY OIL OF DATE 02/03/2021 .PDF	INVENTORY OIL OF DATE 03/03/2021 .PDF
5	INVENTORY OIL OF DATE 01/04/2021 .PDF	INVENTORY OIL OF DATE 02/04/2021 .PDF	INVENTORY OIL OF DATE 03/04/2021 .PDF
6	INVENTORY OIL OF DATE 01/05/2021 .PDF	INVENTORY OIL OF DATE 02/05/2021 .PDF	INVENTORY OIL OF DATE 03/05/2021 .PDF
7	INVENTORY OIL OF DATE 01/06/2021 .PDF	INVENTORY OIL OF DATE 02/06/2021 .PDF	INVENTORY OIL OF DATE 03/06/2021 .PDF
8	INVENTORY OIL OF DATE 01/07/2021 .PDF	INVENTORY OIL OF DATE 02/07/2021 .PDF	INVENTORY OIL OF DATE 03/07/2021 .PDF
9	INVENTORY OIL OF DATE 01/08/2021 .PDF	INVENTORY OIL OF DATE 02/08/2021 .PDF	INVENTORY OIL OF DATE 03/08/2021 .PDF
10	INVENTORY OIL OF DATE 01/09/2021 .PDF	INVENTORY OIL OF DATE 02/09/2021 .PDF	INVENTORY OIL OF DATE 03/09/2021 .PDF
11	INVENTORY OIL OF DATE 01/10/2021 .PDF	INVENTORY OIL OF DATE 02/10/2021 .PDF	INVENTORY OIL OF DATE 03/10/2021 .PDF
12	INVENTORY OIL OF DATE 01/11/2021 .PDF	INVENTORY OIL OF DATE 02/11/2021 .PDF	INVENTORY OIL OF DATE 03/11/2021 .PDF
13	INVENTORY OIL OF DATE 01/12/2021 .PDF	INVENTORY OIL OF DATE 02/12/2021 .PDF	INVENTORY OIL OF DATE 03/12/2021 .PDF
14	INVENTORY OIL OF DATE 01/13/2021 .PDF	INVENTORY OIL OF DATE 02/13/2021 .PDF	INVENTORY OIL OF DATE 03/13/2021 .PDF
15	INVENTORY OIL OF DATE 01/14/2021 .PDF	INVENTORY OIL OF DATE 02/14/2021 .PDF	INVENTORY OIL OF DATE 03/14/2021 .PDF
16	INVENTORY OIL OF DATE 01/15/2021.PDF	INVENTORY OIL OF DATE 02/15/2021.PDF	INVENTORY OIL OF DATE 03/15/2021.PDF
17	INVENTORY OIL OF DATE 01/16/2021 .PDF	INVENTORY OIL OF DATE 02/16/2021 .PDF	INVENTORY OIL OF DATE 03/16/2021 .PDF
18	INVENTORY OIL OF DATE 01/17/2021 .PDF	INVENTORY OIL OF DATE 02/17/2021 .PDF	INVENTORY OIL OF DATE 03/17/2021 .PDF
19	INVENTORY OIL OF DATE 01/18/2021 .PDF	INVENTORY OIL OF DATE 02/18/2021 .PDF	INVENTORY OIL OF DATE 03/18/2021 .PDF
20	INVENTORY OIL OF DATE 01/19/2021 .PDF	INVENTORY OIL OF DATE 02/19/2021 .PDF	INVENTORY OIL OF DATE 03/19/2021 .PDF
21	INVENTORY OIL OF DATE 01/20/2021 .PDF	INVENTORY OIL OF DATE 02/20/2021 .PDF	INVENTORY OIL OF DATE 03/20/2021 .PDF
22	INVENTORY OIL OF DATE 01/21/2021 .PDF	INVENTORY OIL OF DATE 02/21/2021 .PDF	INVENTORY OIL OF DATE 03/21/2021 .PDF
23	INVENTORY OIL OF DATE 01/22/2021 .PDF	INVENTORY OIL OF DATE 02/22/2021 .PDF	INVENTORY OIL OF DATE 03/22/2021 .PDF
24	INVENTORY OIL OF DATE 01/23/2021 .PDF	INVENTORY OIL OF DATE 02/23/2021 .PDF	INVENTORY OIL OF DATE 03/23/2021 .PDF
25	INVENTORY OIL OF DATE 01/24/2021 .PDF	INVENTORY OIL OF DATE 02/24/2021 .PDF	INVENTORY OIL OF DATE 03/24/2021 .PDF
26	INVENTORY OIL OF DATE 01/25/2021 .PDF	INVENTORY OIL OF DATE 02/25/2021 .PDF	INVENTORY OIL OF DATE 03/25/2021 .PDF
27	INVENTORY OIL OF DATE 01/26/2021 .PDF	INVENTORY OIL OF DATE 02/26/2021 .PDF	INVENTORY OIL OF DATE 03/26/2021 .PDF
28	INVENTORY OIL OF DATE 01/27/2021 .PDF	INVENTORY OIL OF DATE 02/27/2021 .PDF	INVENTORY OIL OF DATE 03/27/2021 .PDF
29	INVENTORY OIL OF DATE 01/28/2021 .PDF	INVENTORY OIL OF DATE 02/28/2021 .PDF	INVENTORY OIL OF DATE 03/28/2021 .PDF
30	INVENTORY OIL OF DATE 01/29/2021 .PDF		INVENTORY OIL OF DATE 03/29/2021 .PDF
31	INVENTORY OIL OF DATE 01/30/2021 .PDF		INVENTORY OIL OF DATE 03/30/2021 .PDF
32	INVENTORY OIL OF DATE 01/31/2021 .PDF		INVENTORY OIL OF DATE 03/31/2021 .PDF
2021

if it's not clear please tell me
thanks in advance

 

[John_w]

if I don't save the file as pdf for specific month in specific day then leave the cell and move to another cell then it shows some cells are empty

Sorry, I don't understand you. When you run the macro, it saves the INV sheet as a .pdf file and creates a link to that file in the sheet, column and row related to the current date, so there aren't any empty cells if you run the macro.

 

[leap out]

I expected you don't understand me so look the first image and the second image should be

as you see the image contain empty cell between specific dates it begins from row3 because it matches with second day for date 06/02/2021 when count the row for day of month then it leave row2 because the code considers this the first day of month the same thing for date 06/05/2021 when count the row for day of month then it leave rows from 4:5 because the code considers this the fifth day of month matches with row 6 so I don't want any empty blanks among dates when I have no data every day of month I want the dates under of each other consecutively
first image

TR1.xlsb
	F
1	JUN
2
3	INVENTORY OIL OF DATE 06-02-2021.PDF
4
5
6	INVENTORY OIL OF DATE 06-05-2021.PDF
7
2021

second image

TR1.xlsb
	F
1	JUN
2	INVENTORY OIL OF DATE 06-02-2021.PDF
3	INVENTORY OIL OF DATE 06-05-2021.PDF
4
5
6
7
8
2021

 

[John_w]

I see now - your picture explains it.

Replace:

    With yearSheet
        .Hyperlinks.Add Anchor:=.Cells(Day(currentDate) + 1, Month(currentDate)), Address:=PDFfullName, TextToDisplay:=Mid(PDFfullName, InStrRev(PDFfullName, "\") + 1)
    End With

with:

    With yearSheet
        Dim r As Long
        r = .Cells(.Rows.Count, Month(currentDate)).End(xlUp).row + 1
        .Hyperlinks.Add Anchor:=.Cells(r, Month(currentDate)), Address:=PDFfullName, TextToDisplay:=Mid(PDFfullName, InStrRev(PDFfullName, "\") + 1)
    End With

 

[leap out]

that works , thanks for your cooperate