Consulting

Results 1 to 12 of 12

Thread: convert cells in worksheet to values

  1. #1
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location

    convert cells in worksheet to values

    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!
    Last edited by Ray707; 05-05-2021 at 04:17 AM.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by SamT View Post
    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

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Ray707 View Post
    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=
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by SamT View Post
    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!

  7. #7
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by p45cal View Post
    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!

  8. #8
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by p45cal View Post
    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...

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Ray707 View Post
    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.

    Quote Originally Posted by Ray707 View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #11
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by p45cal View Post
    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.

  12. #12
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    Quote Originally Posted by SamT View Post
    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!

Posting Permissions

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