I have code that allows me to save my worksheet as a PDF, save it to a folder, and also attach it to an email.
within the code I have a “Call” to run DeleteSomeFormulas which removes any formulas within a given range, leaving the data in place.
The code runs perfectly if I run this manually from the Developer tab, but if I apply it to a button it does everything it should except for the removal of formulas.
Sub Email_PDF()
Application.ScreenUpdating = False
Call DeleteSomeFormulas
Dim Path As String, File As String
Path = "Z:TeamStaff CommunicationsStaff BriefingsStaff Briefings 2024" & ""
With ActiveWorkbook: File = Left(.Name, InStr(.Name, ".") - 1): End With
With ActiveSheet
File = "QF28 - Staff Information - Weekly Briefing week commencing " & .Name
With .PageSetup
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
.CenterHorizontally = True
.CenterVertically = False
End With
.Range("A1:F110").ExportAsFixedFormat xlTypePDF, Path & File
End With
With CreateObject("Outlook.Application").CreateItem(0)
.Display
.To = "Technicians"
.Subject = "Briefing Sheet"
.Body = "Please find attached the latest Briefing Sheet."
.Attachments.Add Path & File & ".pdf"
End With
End Sub
DeleteSomeFormulas is:
Sub DeleteSomeFormulas()
With ActiveSheet
Call fileProtection(False)
Range("A2:F9").Copy
Range("A2:F9").PasteSpecial xlPasteValues
Call fileProtection(True)
End With
End Sub
Both pieces of code run perfectly if run manually, and Email_PDF runs from a button, but the DeleteSomeFormulas does not run as a Call when run from the button.
Query also posted here
DarrylBurge is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
6
DeleteSomeFormulas changed to
Sub DeleteSomeFormulas(ws As Worksheet)
Application.ScreenUpdating = False
Call fileProtection(False)
Debug.Print ActiveSheet.Name
With ws.Range("A2:F9")
.Value2 = .Value2
End With
Call fileProtection(True)
Application.ScreenUpdating = True
End Sub
This is then called by
Call DeleteSomeFormulas(ActiveSheet)
DarrylBurge is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.