Moving sheets with macros and buttons into new workbooks

[CLDMister1xbilliontwos]

Hi,


I currently run a macro via a button,
However when I copy the worksheet into a new workbook (which I must do regularly as part of our process with new jobs) , the button remains assigned to the macro in the original workbook and does not change to the macro copied with the new workbook.

For context, we have a workbook with all our cost estimate templates stored. To create a costing for a new job, we copy the required templates into a new workbook specifically for the job.
The macro prints out a temporary PDF quote file. So when i run the button on the new sheet - it ends up printing out a pdf based on information in the original sheet.

Appreciate any help

Cheers

 

[Herakles]

Hi,


I currently run a macro via a button,
However when I copy the worksheet into a new workbook (which I must do regularly as part of our process with new jobs) , the button remains assigned to the macro in the original workbook and does not change to the macro copied with the new workbook.

For context, we have a workbook with all our cost estimate templates stored. To create a costing for a new job, we copy the required templates into a new workbook specifically for the job.
The macro prints out a temporary PDF quote file. So when i run the button on the new sheet - it ends up printing out a pdf based on information in the original sheet.

Appreciate any help

Cheers

Can you please submit the code that you are running using the VBA tags?

Also submit the code that creates the PDF file and the data that this code uses using XL2BB?

VBA and XL2BB can be found on the Mr Excel editor ribbon.

Thanks

.

 

[CLDMister1xbilliontwos]

Sub Button1_Click()
Dim destFolder As String, PDFfile As String
Dim printRange As Range
Dim OutMail As Object


'PDF file for print range is temporarily saved in same folder as this workbook

destFolder = ThisWorkbook.Path & "\"
If Right(destFolder, 1) <> "\" Then destFolder = destFolder & "\"

If ActiveSheet.PageSetup.PrintArea <> "" Then

'Save print area for active sheet as a PDF file, file name from cell E2 and cell I7

PDFfile = destFolder & " SWI Quotation - " & ActiveSheet.Range("k5").Value & "-" & ActiveSheet.Range("k4").Value & " Rev " & ActiveSheet.Range("N5").Value & ".pdf"
Set printRange = Range(ActiveSheet.PageSetup.PrintArea)
printRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

'Send email to address in cell P3 of active sheet with PDF file attached

Set OutMail = CreateObject("Outlook.Application").CreateItem(0)
With OutMail
.To = ActiveSheet.Range("S3").Value
.CC = ActiveSheet.Range("S4").Value
.Subject = ActiveSheet.Range("K5").Value & " - SWI Fencing Quote - " & ActiveSheet.Range("K4").Value
.Body = "Hi, Please find attached quote as requested."
.Attachments.Add PDFfile
.Display
'.Send
End With

'Delete the temporary PDF file

Kill PDFfile