vba - Excel Workbook to PDF Macro Error -

vba - Excel Workbook to PDF Macro Error -

the next code generates invoice number sequence every time open template keeps record of invoice no. i'm to:

option explicit private sub workbook_open() dim refno long dim folder string dim sheetnum integer dim indexsheet string dim fileprefix string dim filesuffix string application.screenupdating = false folder = "c:\users\desktop\" fileprefix = "invoice#" & " " filesuffix = " " & "(draft)" refno = sheets("invoice1").range("nextindex").value 'increment reference number sheets("invoice1").range("nextindex").value = refno + 1 'write new ref no sheet range("thisindex").value = refno 'save workbook thisworkbook.save 'create new workbook 1 sheet workbooks.add (1) 'copy sheets template new workbook sheetnum = 1 thisworkbook.sheets.count thisworkbook.sheets(sheetnum).copy after:=activeworkbook.sheets(sheetnum) next 'blank next ref no doesn't saved in new workbook/sheet activeworkbook.worksheets("invoice1").range("nextindex").clearcontents 'delete default sheet new workbook application.displayalerts = false activeworkbook.sheets("sheet1").delete application.displayalerts = true 'select info sheet create active activeworkbook.sheets("invoice1").select 'save workbook new reference number name activeworkbook.saveas folder & fileprefix & refno & filesuffix & ".xlsx", xlopenxmlworkbook application.screenupdating = true 'close template workbook thisworkbook.close savechanges:=false end sub

however, when add together next macro, creates file template

sub savepdf() activesheet.exportasfixedformat type:=xltypepdf, _ filename:="c:\pdf\export.pdf", _ openafterpublish:=false end sub

for example, when open template above code, , run macro assigned saving file pdf, save pdf create template file. help appreciated. source from: http://www.myonlinetraininghub.com/macro-enabled-excel-templates

ok @delc have solution not particularly elegant. issue de-couple 'sub savepdf' invoice template file. solution puts sub in newly created invoice workbook. biggest downside new invoice workbook macro-enabled workbook! note have adjusted .pdf output page 1 only, button , named range cells don't copied .pdf file. can alter if desired in sec piece of code. see have renamed 'sub savepdf'.

referring invoice template workbook:

replace button on 'invoice1' sheet shape , phone call pdf (edit text).

replace current code in thisworkbook module code below:

option explicit private sub workbook_open() dim refno long dim folder string dim sheetnum integer dim indexsheet string dim fileprefix string dim filesuffix string application.screenupdating = false folder = "c:\users\desktop\" fileprefix = "invoice#" & " " filesuffix = " " & "(draft)" refno = sheets("invoice1").range("nextindex").value 'increment reference number sheets("invoice1").range("nextindex").value = refno + 1 'write new ref no sheet range("thisindex").value = refno 'save workbook thisworkbook.save 'create new workbook 1 sheet workbooks.add (1) 'copy sheets template new workbook sheetnum = 1 thisworkbook.sheets.count thisworkbook.sheets(sheetnum).copy after:=activeworkbook.sheets(sheetnum) next 'blank next ref no doesn't saved in new workbook/sheet activeworkbook.worksheets("invoice1").range("nextindex").clearcontents 'assign macro button activeworkbook.sheets("invoice1").shapes("pdf").select selection.onaction = activeworkbook.name & "!sheet1.pdf_click" 'delete default sheet new workbook application.displayalerts = false activeworkbook.sheets("sheet1").delete application.displayalerts = true 'select info sheet create active activeworkbook.sheets("invoice1").select 'save workbook new reference number name activeworkbook.saveas filename:=folder & fileprefix & refno & filesuffix & ".xlsm", fileformat:=52 application.screenupdating = true 'close template workbook thisworkbook.close savechanges:=false end sub

in sheet1 module set next code:

sub pdf_click() sheets("invoice1").exportasfixedformat type:=xltypepdf, _ filename:=application.activeworkbook.path & "\export.pdf", _ openafterpublish:=false, from:=1, to:=1 activeworkbook.save end sub

save , run it!

other options may create 'sub savepdf' udf , actioned key-combination or set button on excel ribbon run it.

excel vba excel-vba pdf