1) How to save a range as a PDF
The fNAME string is created from the Location plus the Area plus the date. The file is saved in a known folder location fPATH.
Option Explicit
Sub SaveAsPdf()
Dim fPATH As String, fNAME As String
fPATH = "C:\2013\PDF\" 'remember the final \ in this string
With ActiveSheet
fNAME = .[C42] & "-" & .[D42] & "-" & Format(.[B42], "YYYYMMDD") & ".pdf"
.Range("D8:G40").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=fPATH & fNAME, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End With
End Sub
2) Attach that file to an email
Option Explicit
Sub SaveAsPdfAndEmail()
Dim fPATH As String, fNAME As String, eADDR As String
Dim OutApp As Object, OutMail As Object
Do
eADDR = Application.InputBox("Enter email address", _
"Email", "[email protected]", Type:=2)
If eADDR = "False" Then Exit Sub
If InStr(eADDR, "@") > 0 And InStr(eADDR, ".") > 0 Then Exit Do
Loop
fPATH = "C:\2013\PDF\" 'remember the final \ in this string
With ActiveSheet
fNAME = .[C42] & "-" & .[D42] & "-" & Format(.[B42], "YYYYMMDD") & ".pdf"
.Range("D8:G40").ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=fPATH & fNAME, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False 'True is you want to see the PDF
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = eADDR
.CC = ""
.BCC = ""
.Subject = "This it the subject of the email: " & fNAME
.Attachments.Add fPATH & fNAME
.Display 'or use .Send
End With
End Sub
3) Rotate sheet2 values to "Cumulative" section
Put this code in your command button
Private Sub CommandButton1_Click()
With Sheets("Sheet2").Range("A1").CurrentRegion
If .Rows.Count > 1 Then
.Offset(1).Copy
Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats
.Offset(1).ClearContents
End If
End With
End Sub
4) Totals on Sheet9:
D71: =COUNT(Sheet2!K:K)
E71: =AVERAGE(Sheet2!K:K)
Bookmarks