Remove formulas from all worksheets in Excel using VBA

0 votes

I'm having trouble getting Excel to remove formulas from cells and keep simply the value (in case there is a number). The fact that the various spreadsheets also contain pivot tables and GETPIVOTDATA cells is the cause of the issue.

I am currently trying this code but it only works on normal spreadsheets:

Sub fun()

Dim ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pc As PivotCell

For Each ws In ThisWorkbook.Worksheets
    ws.Activate
    With ws.UsedRange
        .Value = .Value
    End With
Next ws


For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables
        For Each pf In pt.PivotFields
            For Each pi In pf.PivotItems
                pi.Value = pi.Value
            Next pi
        Next pf
    Next pt
Next ws

End Sub

Could you help me to adapt the code so that every cell will be set to value?

Oct 3, 2022 in Others by Kithuzzz
• 27,940 points
267 views

1 answer to this question.

0 votes

Try this :

Option Explicit

Sub test1()
    Dim ws As Worksheet, a, area As String
    For Each ws In ThisWorkbook.Worksheets
        a = ws.UsedRange
        area = ws.UsedRange.Address
        ws.Cells.ClearContents
        ws.Range(area) = a
    Next
End Sub
answered Oct 3, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
206 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
374 views
0 votes
1 answer
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,640 points
168 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,600 points
179 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 51,600 points
263 views
0 votes
1 answer

Insert pie chart in Excel macro function

Think about arranging your data in a ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 51,600 points
283 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,600 points
186 views
0 votes
1 answer

How to remove borders from cells in a range in Excel using VB.net?

range.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone range.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle ...READ MORE

answered Jan 5 in Others by narikkadan
• 51,600 points
79 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP