PDA

View Full Version : Save active sheet only as pdf + Increment the invoice number + print active sheet



sarkice
08-01-2017, 01:39 AM
Dear,

I am having issues with macro in Excel 2011 for MAc.

I am trying to save "Invoice" sheet as pdf (name "c5"+"E2") + Increment the invoice number in "E2" + print 2 copies of same sheet. Is it possible?

My code is:


Sub SaveInvoicePrint ()

' Print

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=2


Dim NewFN As Variant
' Create the PDF First
NewFN = "Macintosh HD:Users:Erik 1:Desktop:Brin:Racuni2017:RacunAvt017:" & Range (“C5”).Value & Range("E2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

' Increment the invoice number
Range("E2").Value = Range("E2").Value + 1
End Sub

Can you please help solve issue?! I am getting error. What am I doing wrong?

Thank you in advance!!

offthelip
08-01-2017, 01:50 AM
Have you tried the obvious way:
just use a bit of copy and paste in the code ( i haven't tested this)


Sub SaveInvoicePrint ()

' Print

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1


Dim NewFN As Variant
' Create the PDF First
NewFN = "Macintosh HD:Users:Erik 1:Desktop:Brin:Racuni2017:RacunAvt017:" & Range (“C5”).Value & Range("E2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

' Increment the invoice number
Range("E2").Value = Range("E2").Value + 1


ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1


Dim NewFN As Variant
' Create the PDF First
NewFN = "Macintosh HD:Users:Erik 1:Desktop:Brin:Racuni2017:RacunAvt017:" & Range (“C5”).Value & Range("E2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

' Increment the invoice number
End Sub

sarkice
08-01-2017, 02:01 AM
I just tried. I got Compile Error...

offthelip
08-01-2017, 02:21 AM
Deletethe "Dim Newfn as variant" where is occurs for the second time, that will cause a compile error
sorry.

sarkice
08-01-2017, 02:34 AM
Now i get error 1004:

Method "Range" object "_Global" failed

When debug this line is yellow: NewFN = "Macintosh HD:Users:Erik 1:Desktop:Brin:Racuni2017:RacunAvt017:" & Range(“C5”).Value & Range("E2").Value & ".pdf"

offthelip
08-01-2017, 03:03 AM
Hi, unfortunately I am not familiar with mac 2011 veriosn but what I would do is go back to your original code and jsut call it twice by running the loopprint macrolike thius:


sub loopprint
for i = 1 to 2
call saveinvoicePrint
' Increment the invoice number
Range("E2").Value = Range("E2").Value + 1
next i
end sub

Sub SaveInvoicePrint ()

' Print

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1


Dim NewFN As Variant
' Create the PDF First
NewFN = "Macintosh HD:Users:Erik 1:Desktop:Brin:Racuni2017:RacunAvt017:" & Range (“C5”).Value & Range("E2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

End Sub

sarkice
08-01-2017, 06:29 AM
Your first solution works. I have had wrong quotation marks :/

The only problem is that it saves twice with two following numbers. In C5 i have name and in E2 i have number. So it saves for exampe erik123 and erik124 in two different files. I need just first, of course...

Any other idea?

Thanks!

offthelip
08-01-2017, 07:20 AM
I am not clear what you want.