Results 1 to 2 of 2

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

  1. #1

    Thread Starter
    Registered User
    Join Date
    Jun 2019

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

    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
    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
    ' .Send
    End With
    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,


  2. #2
    Join Date
    Dec 2004

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

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts


Click Here to Expand Forum to Full Width