Modify macro

noelmus

Board Regular
Joined
Dec 30, 2018
Messages
93
Hi,
I have the macro below so that it saves a copy of an excel sheet to pdf in folder named "chits". I need that the file name will be as cell J8. What I have to change in the macro?

Thanks in advance

Code:
Sub Macro1()'
' Macro1 Macro
'


'
    ChDir "C:\Users\littl\Desktop\chits"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\littl\Desktop\chits\16 - adv5 - Copy.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,061
Try:
Code:
Sub Macro1() 
    ChDir "C:\Users\littl\Desktop\chits"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\littl\Desktop\chits\" & Range("J8").Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End Sub
 
Last edited:

noelmus

Board Regular
Joined
Dec 30, 2018
Messages
93
Hi mumps,
It gave me an error and this part became highlighted in yellow.
Code:
 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _        "C:\Users\littl\Desktop\chits\" & Range("J8").Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,061
In your response, the code is on 3 lines. Is that the way it appears in your code module in the workbook or is it on 4 lines as I posted it?
 

noelmus

Board Regular
Joined
Dec 30, 2018
Messages
93
Hi,
Don't know how appeared like that but in my code module is on 4 lines.
Thanks
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,061
Try:
Code:
Sub SavePDF()
    ChDir "C:\Users\littl\Desktop\chits\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("J8").Value _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,939
Office Version
2007
Platform
Windows
If you have a date in cell J8 then you have to change the "/" to "-"
To make the code clearer, it could look like this:

Code:
Sub Macro1()
    ruta = "C:\Users\littl\Desktop\chits\"
    arch = Format(Range("J8").Value, "mm-dd-yyyy")
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ruta & arch & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub
 

noelmus

Board Regular
Joined
Dec 30, 2018
Messages
93
Hi mumps and Dante,
Mumps the same, it gave me again those 4 lines in highlighted yellow.
Dante in cell J8 I have this number 000123/19 which means invoice number and year and gave me these 4 lines in highlighted yellow.

Code:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _        Filename:=ruta & arch & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
Thanks
 

noelmus

Board Regular
Joined
Dec 30, 2018
Messages
93
Hi,
I tried another a cell instead of J8 which has just a number and it worked. I tried another cell with a formula and worked too. So I think that the problem is that cell J8 change its value by double click cell K8.
Regards
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,724
Office Version
365, 2010
Platform
Windows, Mobile
/ is an illegal character in a filename.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,141
Messages
5,472,753
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top