VBA code for new file names

[Bhuvan Kumar]

I created an excel workbook which contains 2 sheet. In one sheet there is data. From the 1 sheet a bill format is made by giving v lookup.

I went for a macro for printing the bills whereas the bill created is replacing the earlier.

This is the code.
Sub PrintBill2()
'
' PrintBill2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Sheets(" Bill of Supply").Select
Range("A2:I2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlUp)).Select
Range("A2:I36").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\PC-1\Documents\Downloads\1.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub


I want a file name based on invoice number which changes from invoice to invoice.

let me know what changes i need to done in the code.

 

[C with no eyes]

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _        " [I][B]C:\Users\PC-1\Documents\Downloads\[/B][/I]" & Format(Range("G1").Value, "yy-mm-dd") & " Daily.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

 

[C with no eyes]

Whoops, pressed post, too early, by accident.

    ' save pdf

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "S:\Month & Year End Documents\Sales Ledger Month End\Daily Turnover\" & Format(Range("G1").Value, "yy-mm-dd") & " Daily.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True


[COLOR=#333333]

This is one I use at work. Cell G1 contained today's date so would save a new one with a unique name, or overwrite a particular date if amendments are made. Basically you'd need to reference a unique cell e.g. invoice ref, and format it. This macro exports our daily turnover to PDF and gives it a unique name e.g. 18-01-29 Daily.

I was also going to say that all this:

[/COLOR][I][B]Range("A2:I2").Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlDown)).Select[/B]
[I][B]Range(Selection, Selection.End(xlUp)).Select[/B][/I][/I][/I][/I][/I][/I][/I][/I][/I][/I][/I][/I][/I][COLOR=#333333]

[/COLOR]
is irrelevant. Delete it.

 

[Bhuvan Kumar]

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _        " [I][B]C:\Users\PC-1\Documents\Downloads\[/B][/I]" & Format(Range("G1").Value, "yy-mm-dd") & " Daily.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

If i am changing the code it i am getting an error as below

Run Time error '-2147024773 (8007007b)':

Document not saved.

 

[C with no eyes]

If i am changing the code it i am getting an error as below

Run Time error '-2147024773 (8007007b)':

Document not saved.

It was posted in error. Did you see my next post?

 

[C with no eyes]

' save pdf

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        [COLOR=#ff0000]"S:\Month & Year End Documents\Sales Ledger Month End\Daily Turnover\"[/COLOR] & [COLOR=#0000ff]Format(Range("G1").Value, "yy-mm-dd") [/COLOR]&[COLOR=#0000ff] " Daily.pdf"[/COLOR] _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True

The red text is the path. The blue text is the name of the file. It works for me in word 2010 and 2013. My file name is created by looking at the value of cell G1, putting it into the format yy-mm-dd then adding daily.pdf. So today it saved a pdf of yesterday's turnover in my daily turnover folder called: 18-02-12 Daily

Try

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        [COLOR=#ff0000]"C:\Users\PC-1\Documents\Downloads\"[/COLOR] &[COLOR=#0000ff] "Invoice " [/COLOR]& [COLOR=#0000ff]Range("G1").Value [/COLOR]&[COLOR=#0000ff] ".pdf"[/COLOR] _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True