Consulting

Results 1 to 6 of 6

Thread: Exclude Sheet from Formula

  1. #1
    VBAX Newbie
    Joined
    Oct 2021
    Posts
    3
    Location

    Exclude Sheet from Formula

    Hi all,

    I'm an excel noob and I've been using this script I found online for a few years now to remove Formula's by just doing a mass copy/paste as value.

    I was wondering if there's an IF statement or something I can add to it to exclude a sheet IF name equals true.

    Sub Formula_Zapper()
     Worksheets.Select
     Cells.Select
     Selection.Copy
     Selection.PasteSpecial Paste:=xlPasteValues
     ActiveSheet.Select
     Application.CutCopyMode = False
    End Sub
    If there is a better way of doing the above I'm also open to suggestions
    Last edited by Aussiebear; 10-17-2021 at 11:30 AM. Reason: Added code tags to supplied code

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    776
    Location
    Sub test()
        If ActiveSheet.Name <> "Sheet1" Then
            MsgBox "Sheet1"
        Else
            MsgBox "Other sheet"
        End If
    End Sub

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,040
    Location
    I'd do something like this


    Option Explicit
    
    
    Sub Formula_Zapper()
        Dim ws As Worksheet
        
        
        With ActiveWorkbook
            If .ProtectWindows Or .ProtectStructure Then
                MsgBox "This workbook is password protected"
                Exit Sub
            End If
        End With
        
        Application.ScreenUpdating = False
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                If UCase(.Name) = "NOTTHISONE" Then GoTo NextWS
                If .ProtectContents Then GoTo NextWS
                
                .UsedRange.Value = .UsedRange.Value
            End With
    NextWS:
        Next
    
    
        Application.ScreenUpdating = True
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Newbie
    Joined
    Oct 2021
    Posts
    3
    Location
    Hi Paul,

    I tried what you put above, however it gave a 1004 error due a sheet containing a Pivot Table. The sheet I want to exclude contains a Pivot Table, and another sheet I don't want to exlcude from the formular contains a Pivot table.

    Would I also need to exclude the second sheet that contains Pivot Tables?

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Posts
    8,040
    Location
    Not sure I followed that, but to not delete a sheet with a PT on it, just add the marked line

    Option Explicit
    
    
    Sub Formula_Zapper()
        Dim ws As Worksheet
        
        
        With ActiveWorkbook
            If .ProtectWindows Or .ProtectStructure Then
                MsgBox "This workbook is password protected"
                Exit Sub
            End If
        End With
        
        Application.ScreenUpdating = False
        
        For Each ws In ActiveWorkbook.Worksheets
            With ws
                If UCase(.Name) = "SHEET1" Then GoTo NextWS
                If .ProtectContents Then GoTo NextWS
                If .PivotTables.Count > 0 Then GoTo NextWS  '   <<<<<<<<<<<<<
                
                .UsedRange.Value = .UsedRange.Value
            End With
    NextWS:
        Next
    
    
    
    
        Application.ScreenUpdating = True
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Newbie
    Joined
    Oct 2021
    Posts
    3
    Location
    Hi Paul,

    Perfect that did the trick. Thanks for your help!

Posting Permissions

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