Vba code:active ws copy paste special to new ws then email OUTLOOK

[BernieStar]

Hi there, I am new to VBA coding (using excel 2016) and was hoping someone might be able to help me. In my activeWorkbook I have a pivot table which people make selections via drop downs prior to running macro button. Current problem is macro copies entire sheet across to new workbook I am hoping to be able to add: copy paste special values into the new work book under the following code:

Sub Email_BookingSheet()
'Update 20180109
Dim xFile As String
Dim xFormat As Long
Dim Wb As Workbook
Dim Wb2 As Workbook
Dim FilePath As String
Dim FileName As String
Dim OutlookApp As Object
Dim OutlookMail As Object
On Error Resume Next
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Wb = Application.ActiveWorkbook
Set Wb2 = Application.ActiveWorkbook
Select Case Wb.FileFormat
Case xlOpenXMLWorkbook:
xFile = ".xlsx"
xFormat = xlOpenXMLWorkbook
Case xlOpenXMLWorkbookMacroEnabled:
If Wb2.HasVBProject Then
xFile = ".xlsm"
xFormat = xlOpenXMLWorkbookMacroEnabled
Else
xFile = ".xlsx"
xFormat = xlOpenXMLWorkbook
End If
Case Excel8:
xFile = ".xls"
xFormat = Excel8
Case xlExcel12:
xFile = ".xlsb"
xFormat = xlExcel12
End Select
FilePath = Environ$("temp") & ""
FileName = Wb.Name & Format(Now, "dd-mmm-yy h-mm-ss")
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
Wb2.SaveAs FilePath & FileName & xFile, FileFormat:=xFormat
With OutlookMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Richlands Dispatch Record 2019 - Booking Sheet"
.Body = " "
.Attachments.Add Wb2.FullName
.Display
' .Send
End With
Wb2.Close
Kill FilePath & FileName & xFile
Set OutlookMail = Nothing
Set OutlookApp = Nothing
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

So that the workbook emailed to clients is a "flat file" so to speak that they are unable to use filters but the information and formatting is copied across to new workbook only. Is this possible?

Many thanks,

B.

[westconn1]

Set Wb = Application.ActiveWorkbook
Set Wb2 = Application.ActiveWorkbook

this of course makes wb the same as wb2
you need

Set Wb = Application.ActiveWorkbook
Set Wb2 = Appication.Workbooks.Add

then you would need to copy the pivot table and paste to sheet 1
something like

Set p = wb.Sheets("my sheet").PivotTables("pivottable1")
p.TableRange2.Copy wb2.Sheets(1).Range("e11")

this is untested
change variable names, sheet and pivottable names to suit, also the target range