VBA Excel final piece to the puzzle, hopefully removing formulas NOT converting table

cizzett

Board Regular
Joined
Jan 10, 2019
Messages
121
So I have been using the below VBA script to remove the formulas from my worksheet when im preparing to save and distribute the workbook but now that i am using a table instead of a range to feed the pivots etc I cannot use this same one.

When I run this it still removes all the formulas as desired and leaves the values in the fields but this also converts the table to a range.

Anyone have an idea how I can do the same thing, remove all formulas and leave the values but not convert my table to a range?

Code:
Sub RemoveFrmls()

    Application.ScreenUpdating = False
        Cells.EntireColumn.Hidden = False


     ' Remove Formulas
    Dim ws As Worksheet
       Set ws = ThisWorkbook.Sheets("Today")
        ws.UsedRange.Value = ws.UsedRange.Value
        
        Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try

Code:
Sub RemoveFrmls()


    Application.ScreenUpdating = False
    Cells.EntireColumn.Hidden = False
    ' Remove Formulas
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Today")
    ws.Cells.Copy
    ws.Range("A1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,654
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top