Record the contents of a cell to be pasted into a "Save As" dialogbox using a macro

[rbrown999]

I have a spreadsheet that I'm using to create sales quotes. Quotes
should have a unique quote number. If you change an expiration date,
you should have a new quote number. If you change a quantity, you
should have a new quote number. So ... I created a quote number that
is randomly (or as best as Excel can do) generated each time there's a
calculation done on a quote. What I want to do is print that quote to
a PDF for distribution, so I make a copy of the worksheet (so I don't
screw up my template) then copy the entire worksheet and "paste
special, values" saving my formulas as values (including the quote
number). When you print to a PDF, you're prompted with a "Save As"
dialog box and I want that PDF filename to be the quote number. So I
highlight the cell containing the quote number, copy the contents of
that cell, and paste it into the filename when the print to PDF dialog
opens up. However, it never seems to either copy the contents or
paste them (don't know which). I've tried highlighting the contents
of the cell in the editing field above the cellls and I've tried to
copy the cell itself. I've tried both the keyboard & mouse
shortcuts. In any event, the filename is never pasted in the "Save
As" dialog box

So my bottom line question is: How do I tell a macro to copy the
contents of a cell that will be different each time, so that it can
paste it in the next dialog box? I'm using Excel 2007 in XP. My
recorded macro is:



Sub PrintQuote()
'
' PrintQuote Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Sheets("QuoteTool").Select
Sheets("QuoteTool").Copy After:=Sheets(2)
Sheets("QuoteTool (2)").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("H8:I8").Select
ActiveCell.FormulaR1C1 = "QUO-DWGLG-59891"
Range("D8").Select
Application.ActivePrinter = "Adobe PDF on Ne04:"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on
Ne04:"",,TRUE,,FALSE)"
Sheets("QuoteTool (2)").Select
ActiveWindow.SelectedSheets.Delete
Sheets("QuoteTool").Select
End Sub

 

[Shane Devenshire]

Hi,

suppose the cell containing the name you want to use in A1 on sheet1:

Dim FN as String
FN = Sheets("Sheet1").Range("A1")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Shane\Documents\" & FN & ".pdf",
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _
True

 

[rbrown999]

Hi,

suppose the cell containing the name you want to use in A1 on sheet1:

    Dim FN as String
    FN = Sheets("Sheet1").Range("A1")
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Shane\Documents\" & FN & ".pdf",
Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _
        True

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire







- Show quoted text -

Would your suggested text be inserted in my macro somewhere in
particular or would yours be a different macro all together? I'm
sorry, I'm just not sure I understand how I would use that. I'm
pretty perficiant at formulas, but have never done any VB programming
in Excel - just macros. Can you dumb it down a little bit for this
particular dummy?