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]

See if this macro works for you. The InputBox line allows you to test the macro with different dates - comment out this line when you've finished testing and it will always use the current date. Forward slashes are invalid in file names, so I've used hyphens instead ("INVENTORY OIL OF DATE 05-22-2021 .PDF").

Public Sub Save_INV_As_PDF()

    Dim currentDate As Date
    Dim baseFolder As String
    Dim PDFfullName  As String
    Dim yearSheet As Worksheet
    
    currentDate = Date
    currentDate = CDate(InputBox("Enter test date", "Testing Only", Default:=Date))  'COMMENT OUT THIS LINE AFTER TESTING
    
    baseFolder = "D:\process\inventory\"
    If Right(baseFolder, 1) <> "\" Then baseFolder = baseFolder & "\"
    
    PDFfullName = baseFolder & UCase(Format(currentDate, "YYYY\\MMM\\")) & "INVENTORY OIL OF DATE " & Format(currentDate, "mm-dd-yyyy") & ".PDF"
    
    With ThisWorkbook
        
        'Save INV sheet as PDF
        
        .Worksheets("INV").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
        'Add current year YYYY sheet if it doesn't exist
        
        Set yearSheet = Nothing
        On Error Resume Next
        Set yearSheet = .Worksheets(CStr(Year(currentDate)))
        On Error GoTo 0
        If yearSheet Is Nothing Then
            Set yearSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            yearSheet.Name = CStr(Year(currentDate))
            yearSheet.Range("A1").Resize(, 12).Value = Split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC")
        End If
        
    End With
    
    'Create hyperlink to PDF in YYYY year sheet and row/column for current date
    
    With yearSheet
        .Hyperlinks.Add Anchor:=.Cells(Day(currentDate) + 1, Month(currentDate)), Address:=PDFfullName, TextToDisplay:=Mid(PDFfullName, InStrRev(PDFfullName, "\") + 1)
    End With
    
End Sub

 

[leap out]

thanks so much for your solution

sorry I said that but if you did it I truly appreciate you
may you create folder 2021 and subfolders JAN,FEB up to DEC and if finish this year and enter a new year then create folder 2022 and subfolders JAN,FEB and so on for every a new year should create folder the year and subfolders the months automatically ?
I don't want to do that manually if you add some lines to your code it will be a great

 

[leap out]

please ignore post#3 I don't want making confusion for you

first of all I would thank you for your solution

the second , I note if I use the line about inputbox and change any date not today it shows error message I get is Run-Time Error '1004' Document not saved. The document may be open or an error may have been encountered in this line

 .Worksheets("INV").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, Quality:=xlQualityStandard, _

            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

and sorry about I don't mentioned that

may you create folder 2021 and subfolders JAN,FEB up to DEC and if finish this year and enter a new year then create folder 2022 and subfolders JAN,FEB and so on for every a new year should create folder the year and subfolders the months automatically ?

I don't want to do that manually if you add some lines to your code it

finally when create sheet current year it should create headers months automatically

and I want open the file directly when press the hyperlink not show two choices to open the file

if you can mod this adjusting it will be a great and sorry again

 

[John_w]

thanks so much for your solution

sorry I said that but if you did it I truly appreciate you
may you create folder 2021 and subfolders JAN,FEB up to DEC and if finish this year and enter a new year then create folder 2022 and subfolders JAN,FEB and so on for every a new year should create folder the year and subfolders the months automatically ?
I don't want to do that manually if you add some lines to your code it will be a great

I'm not sure about the wink. All my own work - promise! It doesn't create the subfolders because you said they already exist.

Here's the macro updated to create the year and month subfolders for the current date, if they don't exist.

Public Sub Save_INV_As_PDF()

    Dim currentDate As Date
    Dim baseFolder As String
    Dim YearMonthSubfolder As String
    Dim PDFfullName  As String
    Dim yearSheet As Worksheet
    
    currentDate = Date
    currentDate = CDate(InputBox("Enter test date", "Testing Only", Default:=Date))  'COMMENT OUT THIS LINE AFTER TESTING
    
    baseFolder = "D:\process\inventory\"
    If Right(baseFolder, 1) <> "\" Then baseFolder = baseFolder & "\"
    
    YearMonthSubfolder = baseFolder & Year(currentDate) & "\"
    If Dir(YearMonthSubfolder, vbDirectory) = vbNullString Then MkDir YearMonthSubfolder
    
    YearMonthSubfolder = YearMonthSubfolder & UCase(Format(currentDate, "MMM")) & "\"
    If Dir(YearMonthSubfolder, vbDirectory) = vbNullString Then MkDir YearMonthSubfolder
    
    PDFfullName = YearMonthSubfolder & "INVENTORY OIL OF DATE " & Format(currentDate, "mm-dd-yyyy") & ".PDF"
    
    With ThisWorkbook
        
        'Save INV sheet as PDF
        
        .Worksheets("INV").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        
        'Add current year YYYY sheet if it doesn't exist
        
        Set yearSheet = Nothing
        On Error Resume Next
        Set yearSheet = .Worksheets(CStr(Year(currentDate)))
        On Error GoTo 0
        If yearSheet Is Nothing Then
            Set yearSheet = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            yearSheet.Name = CStr(Year(currentDate))
            yearSheet.Range("A1").Resize(, 12).Value = Split("JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC")
        End If
        
    End With
    
    'Create hyperlink to PDF in YYYY year sheet and row/column for current date
    
    With yearSheet
        .Hyperlinks.Add Anchor:=.Cells(Day(currentDate) + 1, Month(currentDate)), Address:=PDFfullName, TextToDisplay:=Mid(PDFfullName, InStrRev(PDFfullName, "\") + 1)
    End With
    
End Sub

the second , I note if I use the line about inputbox and change any date not today it shows error message I get is Run-Time Error '1004' Document not saved. The document may be open or an error may have been encountered in this line

.Worksheets("INV").ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfullName, Quality:=xlQualityStandard, _

IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

What are you entering for the date? Remember, the InputBox line is only for testing the macro with different dates so that you don't have to change the computer's clock to test it. The code expects the date to be input in your standard system date format and does no validation or checking of the input, e.g. you'll get an error if you click Cancel. You'll get the Run-Time Error '1004' Document not saved error if the year or month subfolders don't exist, but the updated macro should fix that.

 

[leap out]

wow! this is the most best answering , you gives me a big favor
you've modified every thing what I ask for you
you are super great !
many thanks for a huge assistance

 

[Hasson]

@John_w
your code is excellent , but I search way to mod the code by add in directory folder name's BACKUP and create subfolders based on current year 2021 and create subfolders based on current month JAN,FEB etc. and save the file as XLSM but keep in your mind with stay the original code without change because I need also save the file as pdf to file 2021

 

[John_w]

@John_w
your code is excellent , but I search way to mod the code by add in directory folder name's BACKUP and create subfolders based on current year 2021 and create subfolders based on current month JAN,FEB etc. and save the file as XLSM but keep in your mind with stay the original code without change because I need also save the file as pdf to file 2021

I think it would be better to start a new thread because your request for additional functionality diverges from the OP's request.

 

[Hasson]

@John_w thanks I will and hope to see my thread as soon as you have free time if you can help

 

[leap out]

@John_w sorry I came back again , but I note 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 if I don't save in specific days because the cells are linked with days of months as code does it so if it's possible it shouldn't leave any blanks among cells
I hope my idea is clear