PDF Macro - PLEASE HELP!!!

[jcb267]

Hi -

I have a macro that will create a PDF file for archival purposes. I can get the macro to work fine, however I want to refer to a specific cell to include a name in the PDF file.

For example, currently, the macro will produce a PDF file called "CARE/PDF" I want to alter the macro to include a name in PDF file name, like "FRED_CARE.PDF". The name fred appears in cell b4 of the "CARE" worksheet.

This is what I have:

' MakePDF Macro
'
' Keyboard Shortcut: Ctrl+a
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"F:\HOME\COMMERCIAL\SMALL GROUP RATE MODELS\PDF Files\Care.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

Can anyone please help me??

Thanks,

JB

[carden2]

Will the name in B4 change? If not just put the name you want for care. You could also just run the command to close the file and it will prompt the user to Save.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"F:\HOME\COMMERCIAL\SMALL GROUP RATE MODELS\PDF Files\Name_Care.pdf", Quality:= _

[jcb267]

That is the problem, it can change!

[Chippy]

Change the file name argument to:

Please Login or Register  to view this content.

[jcb267]

I tried changing it but get an error that says - Compile Error: Syntax Error......

[Chandrajit]

You should try this and please let us know!

Please Login or Register  to view this content.

[carden2]

[jcb267]

I made the change but cannot get it to work.......very frustrating.....

I get a runtime error 438 Object doesnt support this method or property....

[Chandrajit]

tried this?

Please Login or Register  to view this content.

[jcb267]

tried this?

Please Login or Register  to view this content.

Yes, I did try that

[john55]

this one it works fine, did not tried the other one!
please chg the path

Please Login or Register  to view this content.

[jcb267]

I tried this but can't get it to work......here is the code I am using:

On Error Resume Next
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"F:\HOME\COMMERCIAL\SMALL GROUP RATE MODELS\PDF Files\" & ThisWorkbook.Sheets("Rate Sheet Plan Year").Range("B3") & "_ Plan_Year.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

Please note that I did change the sheet and range intentionally!

Thanks!!

[john55]

I suggest you to test the code, let's say on yr desktop, and then change the path for yr needs. also, take care of yr sheet name!

[jcb267]

Thanks, John.
I still cannot get this to work. What is wrong with the sheet name?
JB

[zbor]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[john55]

do you rcv any error?
@zbor, I am sorry, I did not refresh the page, please delete my post!
thx!