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

leap out

Board Regular
Joined
Dec 4, 2020
Messages
141
Office Version
  1. 2016
  2. 2010
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
ABCDE
1ITEMBRANDTYPEMONAFACTUREQ1
2110W40 12x1LCASSU567
3210W40 12x1LENIIT54
4310W40 12x1LQ8EU1234
5410W40 208LCASSU234
6510W40 208LENIIT123
7610W40 208LQ8EU132
8710W40 4x4LCASSU45
9810W40 4x4LENIIT67
10910W40 4x4LQ8EU123
111020W50 4x4LCASSU87
121120W50 4x4LENIIT98
INV1


MONTHS.xlsm
ABCDEFGHIJKL
1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2INVENTORY OIL OF DATE 01/01/2021 .PDFINVENTORY OIL OF DATE 02/01/2021 .PDFINVENTORY OIL OF DATE 03/01/2021 .PDF
3INVENTORY OIL OF DATE 01/02/2021 .PDFINVENTORY OIL OF DATE 02/02/2021 .PDFINVENTORY OIL OF DATE 03/02/2021 .PDF
4INVENTORY OIL OF DATE 01/03/2021 .PDFINVENTORY OIL OF DATE 02/03/2021 .PDFINVENTORY OIL OF DATE 03/03/2021 .PDF
5INVENTORY OIL OF DATE 01/04/2021 .PDFINVENTORY OIL OF DATE 02/04/2021 .PDFINVENTORY OIL OF DATE 03/04/2021 .PDF
6INVENTORY OIL OF DATE 01/05/2021 .PDFINVENTORY OIL OF DATE 02/05/2021 .PDFINVENTORY OIL OF DATE 03/05/2021 .PDF
7INVENTORY OIL OF DATE 01/06/2021 .PDFINVENTORY OIL OF DATE 02/06/2021 .PDFINVENTORY OIL OF DATE 03/06/2021 .PDF
8INVENTORY OIL OF DATE 01/07/2021 .PDFINVENTORY OIL OF DATE 02/07/2021 .PDFINVENTORY OIL OF DATE 03/07/2021 .PDF
9INVENTORY OIL OF DATE 01/08/2021 .PDFINVENTORY OIL OF DATE 02/08/2021 .PDFINVENTORY OIL OF DATE 03/08/2021 .PDF
10INVENTORY OIL OF DATE 01/09/2021 .PDFINVENTORY OIL OF DATE 02/09/2021 .PDFINVENTORY OIL OF DATE 03/09/2021 .PDF
11INVENTORY OIL OF DATE 01/10/2021 .PDFINVENTORY OIL OF DATE 02/10/2021 .PDFINVENTORY OIL OF DATE 03/10/2021 .PDF
12INVENTORY OIL OF DATE 01/11/2021 .PDFINVENTORY OIL OF DATE 02/11/2021 .PDFINVENTORY OIL OF DATE 03/11/2021 .PDF
13INVENTORY OIL OF DATE 01/12/2021 .PDFINVENTORY OIL OF DATE 02/12/2021 .PDFINVENTORY OIL OF DATE 03/12/2021 .PDF
14INVENTORY OIL OF DATE 01/13/2021 .PDFINVENTORY OIL OF DATE 02/13/2021 .PDFINVENTORY OIL OF DATE 03/13/2021 .PDF
15INVENTORY OIL OF DATE 01/14/2021 .PDFINVENTORY OIL OF DATE 02/14/2021 .PDFINVENTORY OIL OF DATE 03/14/2021 .PDF
16INVENTORY OIL OF DATE 01/15/2021.PDFINVENTORY OIL OF DATE 02/15/2021.PDFINVENTORY OIL OF DATE 03/15/2021.PDF
17INVENTORY OIL OF DATE 01/16/2021 .PDFINVENTORY OIL OF DATE 02/16/2021 .PDFINVENTORY OIL OF DATE 03/16/2021 .PDF
18INVENTORY OIL OF DATE 01/17/2021 .PDFINVENTORY OIL OF DATE 02/17/2021 .PDFINVENTORY OIL OF DATE 03/17/2021 .PDF
19INVENTORY OIL OF DATE 01/18/2021 .PDFINVENTORY OIL OF DATE 02/18/2021 .PDFINVENTORY OIL OF DATE 03/18/2021 .PDF
20INVENTORY OIL OF DATE 01/19/2021 .PDFINVENTORY OIL OF DATE 02/19/2021 .PDFINVENTORY OIL OF DATE 03/19/2021 .PDF
21INVENTORY OIL OF DATE 01/20/2021 .PDFINVENTORY OIL OF DATE 02/20/2021 .PDFINVENTORY OIL OF DATE 03/20/2021 .PDF
22INVENTORY OIL OF DATE 01/21/2021 .PDFINVENTORY OIL OF DATE 02/21/2021 .PDFINVENTORY OIL OF DATE 03/21/2021 .PDF
23INVENTORY OIL OF DATE 01/22/2021 .PDFINVENTORY OIL OF DATE 02/22/2021 .PDFINVENTORY OIL OF DATE 03/22/2021 .PDF
24INVENTORY OIL OF DATE 01/23/2021 .PDFINVENTORY OIL OF DATE 02/23/2021 .PDFINVENTORY OIL OF DATE 03/23/2021 .PDF
25INVENTORY OIL OF DATE 01/24/2021 .PDFINVENTORY OIL OF DATE 02/24/2021 .PDFINVENTORY OIL OF DATE 03/24/2021 .PDF
26INVENTORY OIL OF DATE 01/25/2021 .PDFINVENTORY OIL OF DATE 02/25/2021 .PDFINVENTORY OIL OF DATE 03/25/2021 .PDF
27INVENTORY OIL OF DATE 01/26/2021 .PDFINVENTORY OIL OF DATE 02/26/2021 .PDFINVENTORY OIL OF DATE 03/26/2021 .PDF
28INVENTORY OIL OF DATE 01/27/2021 .PDFINVENTORY OIL OF DATE 02/27/2021 .PDFINVENTORY OIL OF DATE 03/27/2021 .PDF
29INVENTORY OIL OF DATE 01/28/2021 .PDFINVENTORY OIL OF DATE 02/28/2021 .PDFINVENTORY OIL OF DATE 03/28/2021 .PDF
30INVENTORY OIL OF DATE 01/29/2021 .PDFINVENTORY OIL OF DATE 03/29/2021 .PDF
31INVENTORY OIL OF DATE 01/30/2021 .PDFINVENTORY OIL OF DATE 03/30/2021 .PDF
32INVENTORY OIL OF DATE 01/31/2021 .PDFINVENTORY OIL OF DATE 03/31/2021 .PDF
2021

if it's not clear please tell me
thanks in advance
 
Last edited by a moderator:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,903
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").

VBA Code:
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

Board Regular
Joined
Dec 4, 2020
Messages
141
Office Version
  1. 2016
  2. 2010
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

Board Regular
Joined
Dec 4, 2020
Messages
141
Office Version
  1. 2016
  2. 2010
please ignore post#3 I don't want making confusion for you

first of all I would thank you for your solution (y)

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

VBA Code:
 .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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,903

ADVERTISEMENT

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.
VBA Code:
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

VBA Code:
.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.
 
Solution

leap out

Board Regular
Joined
Dec 4, 2020
Messages
141
Office Version
  1. 2016
  2. 2010
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 :love:
 
Last edited:

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
87
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,903
@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

Board Regular
Joined
Apr 8, 2021
Messages
87
Office Version
  1. 2016
Platform
  1. Windows
@John_w thanks I will and hope to see my thread as soon as you have free time if you can help
 

leap out

Board Regular
Joined
Dec 4, 2020
Messages
141
Office Version
  1. 2016
  2. 2010
@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
 

Forum statistics

Threads
1,136,805
Messages
5,677,829
Members
419,723
Latest member
jamieherethree

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top