convert cells in worksheet to values

[Ray707]

Hello,

I have a VBA that automatically sends out the first worksheet of my file to stakeholders via a command button. The problem is that the worksheet has pivot tables in it, so when it gets sent, stakeholders can change the filter in the pivot tables and view other departments costs. I need to find a way round this and I am thinking that the best solution is to convert/paste the worksheet cells to values before the email gets generated as this would prevent the filter being available in the pivots - I was wondering if anyone could help with this?

Here is what the code currently looks like:

Private Sub commandbutton1_click()
    'update 20131209
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim sfilepath As String, sfilename As String
    Dim iformat As Integer


    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Set wb1 = Application.ActiveWorkbook
    ActiveSheet.Copy
    Set wb2 = Application.ActiveWorkbook
    
    sfilepath = Environ$("temp")
    sfilename = sfilepath & "\ " & wb1.Name
    iformat = wb1.FileFormat
    wb2.SaveAs sfilename, iformat
    wb2.Close
    
    With CreateObject("outlook.application").createitem(0)
        .to = Sheet7.Range("G3")
        .cc = ""
        .bcc = ""
        .Subject = Sheet7.Range("A5")
        .body = "Hello," & vbLf & vbLf & "Please find attached the YTD third party and time costs." & vbLf & vbLf & "Thanks," & vbLf & "Ray"
        .attachments.Add sfilename
        .send
    End With
    
    Kill sfilename
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
End Sub


-----------------------------------------


Private Sub Worksheet_Change(ByVal Target As Range)


'This line stops the worksheet updating on every change, it only updates when cell
'A1:B2 is touched
If Intersect(Target, Range("A1:B2")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String


'Here you amend to suit your data for first pivot table
Set pt = Worksheets("New Business").PivotTables("PivotTable7")
Set Field = pt.PivotFields("Job No.")
NewCat = Worksheets("New Business").Range("B1").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters


Field.CurrentPage = NewCat
pt.RefreshTable
End With




'Here you amend to suit your data for second pivot table
Set pt = Worksheets("New Business").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Job No.")
NewCat = Worksheets("New Business").Range("B1").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters




Field.CurrentPage = NewCat
pt.RefreshTable
End With


End Sub

Here is a picture of what the worksheet currently looks like when it gets sent, notice that there's access to the filter in the pivot tables: https://ibb.co/Q88Vxxp

Here is a picture of what I would like the worksheet to look like (it's a different worksheet to mine but notice how there is no access to the filters, this is because the values have been hard copied/converted to values): https://ibb.co/7Vh7PNT

Please note, I don't want the cells to stay hard copied in the original file, I only want them hard copied in the email that gets sent out.

Any help would be appreciated!

[SamT]

Dim SINW As Long
SINW = Application.SheetsInNewWorkbook 'Current default
       
    Application.SheetsInNewWorkbook = 1 'Changes current default
       Set wb1 = Application.ActiveWorkbook '<--- Original line of code
          wb1.ActiveSheet.UsedRange.Copy
       Set wb2 = Workbooks.Add
          wb2.Sheet1.Cells(1).PasteSpecial Operation:=xlPasteValuesAndNumberFormats
    Application.SheetsInNewWorkbook = SINW 'Resets current default

[Ray707]

Dim SINW As Long
SINW = Application.SheetsInNewWorkbook 'Current default
       
    Application.SheetsInNewWorkbook = 1 'Changes current default
       Set wb1 = Application.ActiveWorkbook '<--- Original line of code
          wb1.ActiveSheet.UsedRange.Copy
       Set wb2 = Workbooks.Add
          wb2.Sheet1.Cells(1).PasteSpecial Operation:=xlPasteValuesAndNumberFormats
    Application.SheetsInNewWorkbook = SINW 'Resets current default

Hi Sam, thanks for the response.

Sorry to be a noob, but how do I implement that into the first part of my code above?

I tried this but it didn't work:

Private Sub commandbutton1_click()
    'update 20131209
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim sfilepath As String, sfilename As String
    Dim iformat As Integer
    Dim SINW As Long
    
    SINW = Application.SheetsInNewWorkbook 'Current default
       
    Application.SheetsInNewWorkbook = 1 'Changes current default
       Set wb1 = Application.ActiveWorkbook '<--- Original line of code
          wb1.ActiveSheet.UsedRange.Copy
       Set wb2 = Workbooks.Add
          wb2.Sheet1.Cells(1).PasteSpecial Operation:=xlPasteValuesAndNumberFormats
    Application.SheetsInNewWorkbook = SINW 'Resets current default
    
    sfilepath = Environ$("temp")
    sfilename = sfilepath & "\ " & wb1.Name
    iformat = wb1.FileFormat
    wb2.SaveAs sfilename, iformat
    wb2.Close
    
    With CreateObject("outlook.application").createitem(0)
        .to = Sheet7.Range("G3")
        .cc = ""
        .bcc = ""
        .Subject = Sheet7.Range("A5")
        .body = "Hello," & vbLf & vbLf & "Please find attached the YTD third party and time costs." & vbLf & vbLf & "Thanks," & vbLf & "Ray"
        .attachments.Add sfilename
        .send
    End With
    
    Kill sfilename
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
End Sub

[SamT]

You did it right. I meesed up

I don't know why, but it seems one can't use the Sheet Object With PasteSpecial. Anyway, this tested version uses the Worksheet object. I also corrected my brain fart with PasteSpecial Operation:=

    Dim wb1 As Workbook, wb2 As Workbook
    Dim sfilepath As String, sfilename As String
    Dim iformat As Integer
    Dim SINW As Long
    
    SINW = Application.SheetsInNewWorkbook 'Current default
       
    Application.SheetsInNewWorkbook = 1 'Changes current default
       Set wb1 = Application.ActiveWorkbook '<--- Original line of code
          wb1.ActiveSheet.UsedRange.Copy
        Set wb2 = Workbooks.Add
         wb2.Sheets(1).Cells(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats '<--Note changes in red
    Application.SheetsInNewWorkbook = SINW 'Resets current default

When stepping thru the code using F8, the first error was "PasteSPecial failed..." My brain fart. The next error was "Object doesn't support...," so I knew the error was either wb2 or Sheet1, the only two Objects in that line

[p45cal]

when it gets sent, stakeholders can change the filter in the pivot tables and view other departments costs

With a pivot table, there is the option to save/not save the data with the file.
You could try one of the following:
1. With your working/original file make sure all pivot tables that you might send have this option set to not save the data with the file as follows: right-click somewhere in the pivot table and choose PivotTable options…, then in the Data tab look for the checkbox: Save source data with file (it's usually the first checkbox) and make sure it's unticked.
If the source data's in the original file, but not in the file you send this should be OK.
2. When you're creating the file to send, have the vba turn that option off for you in the file you send with the likes of

    ActiveSheet.PivotTables("PivotTable1").SaveData = False

for each pivot table.

Otherwise, to lose the pivot altogether and save only the hard values (make sure the original data isn't still elsewhere in the sent spreadsheet!) you can do the likes of:

With ActiveSheet.PivotTables("PivotTable1").TableRange2
  .Copy
  .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With

But much of this and more was already discussed in a previous thread of yours about a month ago: http://www.vbaexpress.com/forum/show...Cat&highlight=

[Ray707]

You did it right. I meesed up

I don't know why, but it seems one can't use the Sheet Object With PasteSpecial. Anyway, this tested version uses the Worksheet object. I also corrected my brain fart with PasteSpecial Operation:=

    Dim wb1 As Workbook, wb2 As Workbook
    Dim sfilepath As String, sfilename As String
    Dim iformat As Integer
    Dim SINW As Long
    
    SINW = Application.SheetsInNewWorkbook 'Current default
      
    Application.SheetsInNewWorkbook = 1 'Changes current default
       Set wb1 = Application.ActiveWorkbook '<--- Original line of code
          wb1.ActiveSheet.UsedRange.Copy
        Set wb2 = Workbooks.Add
         wb2.Sheets(1).Cells(1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats '<--Note changes in red
    Application.SheetsInNewWorkbook = SINW 'Resets current default

When stepping thru the code using F8, the first error was "PasteSPecial failed..." My brain fart. The next error was "Object doesn't support...," so I knew the error was either wb2 or Sheet1, the only two Objects in that line

Thank you, that worked. The only problem is I lose the formatting in the sheet so for example the colour coding in the tables and the bordering of the cells are all lost, and I would like to keep this. But in terms of hard coding the values it does exactly that so thanks!

[Ray707]

With a pivot table, there is the option to save/not save the data with the file.
You could try one of the following:
1. With your working/original file make sure all pivot tables that you might send have this option set to not save the data with the file as follows: right-click somewhere in the pivot table and choose PivotTable options…, then in the Data tab look for the checkbox: Save source data with file (it's usually the first checkbox) and make sure it's unticked.
If the source data's in the original file, but not in the file you send this should be OK.
2. When you're creating the file to send, have the vba turn that option off for you in the file you send with the likes of

    ActiveSheet.PivotTables("PivotTable1").SaveData = False

for each pivot table.

Otherwise, to lose the pivot altogether and save only the hard values (make sure the original data isn't still elsewhere in the sent spreadsheet!) you can do the likes of:

With ActiveSheet.PivotTables("PivotTable1").TableRange2
  .Copy
  .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With

But much of this and more was already discussed in a previous thread of yours about a month ago: http://www.vbaexpress.com/forum/show...Cat&highlight=

The last solution worked- thank you!

[Ray707]

With a pivot table, there is the option to save/not save the data with the file.
You could try one of the following:
1. With your working/original file make sure all pivot tables that you might send have this option set to not save the data with the file as follows: right-click somewhere in the pivot table and choose PivotTable options…, then in the Data tab look for the checkbox: Save source data with file (it's usually the first checkbox) and make sure it's unticked.
If the source data's in the original file, but not in the file you send this should be OK.
2. When you're creating the file to send, have the vba turn that option off for you in the file you send with the likes of

    ActiveSheet.PivotTables("PivotTable1").SaveData = False

for each pivot table.

Otherwise, to lose the pivot altogether and save only the hard values (make sure the original data isn't still elsewhere in the sent spreadsheet!) you can do the likes of:

With ActiveSheet.PivotTables("PivotTable1").TableRange2
  .Copy
  .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End With

But much of this and more was already discussed in a previous thread of yours about a month ago: http://www.vbaexpress.com/forum/show...Cat&highlight=

@p45cal, I have one more question; the code you provided may be useful for something else I am looking to do which is to disable one pivot table filter in a worksheet to prevent people from looking at other departments data (the source data is in the file therefore the other options outlined will not work). Is there any way of amending your code to do what it currently does, but just for 1 cell? In other words, can we hard copy the value for just A1?

This may not work as intended because I still want people to have access to the other filters in the pivot table- I simply want to remove access to one filter - and if we hard copy the first filter it may mean the rest of the filters do not update, but it may be worth a try in case it does...

[SamT]

Easy. everything except formulas:

    Set wb2 = Workbooks.Add
         With wb2.Sheets(1).Cells(1)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
            .PasteSpecial Paste:=xlPasteColumnWidths
            .PasteSpecial Paste:=xlPasteComments
         End With
    Application.SheetsInNewWorkbook = SINW

[p45cal]

to disable one pivot table filter in a worksheet to prevent people from looking at other departments data (the source data is in the file therefore the other options outlined will not work). Is there any way of amending your code to do what it currently does, but just for 1 cell? In other words, can we hard copy the value for just A1?

No, the pivot table won't let you do that. You can hide the drop down arrow for that pivot field but that doesn't stop anyone from seeing other dept's data by adding/moving the field elsewhere, or adding a slicer.

This may not work as intended because I still want people to have access to the other filters in the pivot table- I simply want to remove access to one filter - and if we hard copy the first filter it may mean the rest of the filters do not update, but it may be worth a try in case it does...

The only way to retain pivot table functionality is to include only the data for that dept in the file you send.
This was discussed in yet another thread of yours here:
http://www.vbaexpress.com/forum/show...l=1#post408588
where in the same thread in msg#15 I showed you how to include only a subset of the data pertaining to only one dept in the files you send out. That will allow your pivot table to operate fully, but there'll only be one dept in the dropdown because other dept's data simply isn't there in the file.

[Ray707]

No, the pivot table won't let you do that. You can hide the drop down arrow for that pivot field but that doesn't stop anyone from seeing other dept's data by adding/moving the field elsewhere, or adding a slicer.

The only way to retain pivot table functionality is to include only the data for that dept in the file you send.
This was discussed in yet another thread of yours here:
http://www.vbaexpress.com/forum/show...l=1#post408588
where in the same thread in msg#15 I showed you how to include only a subset of the data pertaining to only one dept in the files you send out. That will allow your pivot table to operate fully, but there'll only be one dept in the dropdown because other dept's data simply isn't there in the file.

Yeah thought so. Dammnit, was worth a try anyway.

[Ray707]

Easy. everything except formulas:

    Set wb2 = Workbooks.Add
         With wb2.Sheets(1).Cells(1)
            .PasteSpecial Paste:=xlPasteValues
            .PasteSpecial Paste:=xlPasteFormats
            .PasteSpecial Paste:=xlPasteColumnWidths
            .PasteSpecial Paste:=xlPasteComments
         End With
    Application.SheetsInNewWorkbook = SINW

that worked perfectly- thank you!