noelmus

Board Regular
Joined
Dec 30, 2018
Messages
105
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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:
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top