Macro: Designate File name & Print to PDF

[mws4ua]

I did search the forum a bit first, but couldn't find an answer...

I want to create a macro that will select text in a certian cell, then use that text as part of a name when it publishes the sheet to PDF. I used the Macro recorder to create the code below.

Each month I will update the contents of cell B6 (201102 = February 2011, 201103 = March 2011, etc.)

I want to save the analysis on Sheet "18CFA" each month as a PDF, and I want the file name to begin with the 6-digit month identifier ("201103 - Line 18 Analysis.pdf" in this example).

In the current macro, I go to the number in B6, copy it, then go to the sheet I want to save and click the "Save As PDF or XPS" button. I then Paste what was copied from B6 (in this case, "201103", then type the rest of the name and click Publish.

The macro currently does two things wrong. First, when it's finished, the contents of cell B6 are always "201103," and second, obviously, it's not using what I type into B6 as the file name.

Anyway... here's the code:

Sub PrintPDF()
'
' PrintPDF Macro
'
'
Range("B6").Select

ActiveCell.FormulaR1C1 = "201103"

Range("A1").Select

Sheets("18FCA").Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\mstuenke\Desktop\201103 - Line 18 Analysis.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End Sub

 

[VoG]

Try

Sub PrintPDF()
'
' PrintPDF Macro
'
'
Dim d As String
d = Format(Date, "yyyymm")
Sheets("18FCA").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\mstuenke\Desktop\" & d & " - Line 18 Analysis.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End Sub

 

[mws4ua]

Cell B6 no longer changes back to 201103, but the file name is 201103 no matter what I enter into B6...

 

[VoG]

Try entering a date (not text) in B6 and try

Sub PrintPDF()
'
' PrintPDF Macro
'
'
Dim d As String
d = Format(Range("B6").Value, "yyyymm")
Sheets("18FCA").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\mstuenke\Desktop\" & d & " - Line 18 Analysis.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End Sub

 

[mws4ua]

Try entering a date (not text) in B6 and try

WINNER!

Thanks!

 

[mws4ua]

One more quick question...

I actually want to print two sheets into a single PDF document. The sheets are "18CFA" and "18CFA2". What would the "Sheets(..." portion of the code look like if I wanted to print both?

Thanks again, btw!

 

[mws4ua]

I know I'm blowing up my own thread... But I think I figured it out:

Sub PrintPDF()
'
' PrintPDF Macro
'
'
Dim d As String
d = Format(Range("B6").Value, "yyyymm")
Sheets(Array("18FCA", "18FCA2")).Select
Sheets("18FCA").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\mstuenke\Desktop\" & d & " - Line 18 Analysis.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub

 

[VoG]

Maybe

Sub PrintPDF()
'
' PrintPDF Macro
'
'
Dim d As String, ws As Worksheet
d = Format(Range("B6").Value, "yyyymm")
For Each ws In Sheets(Array("18FCA", "18FCA2"))
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Documents and Settings\mstuenke\Desktop\" & d & " - Line 18 Analysis.pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
Next ws
End Sub

 

[mws4ua]

Thanks for responding, but I opened a blank workbook and played w/ the recorder to create the code in post #7. It works pretty well.

Thanks again for your help. I'm a code noob. I need to learn more about the Dim function.