Excel Macro to Paste Values in All Cells in All Worksheets

Use this macro in Excel to convert all of the formulas on all worksheets of a spreadsheet to static values:

Sub Paste() Dim Sh As Worksheet For Each Sh In ThisWorkbook.Worksheets If Sh.Visible = True Then Sh.Activate Sh.Cells.Copy Sh.Range("A1").PasteSpecial Paste:=xlValues Sh.Range("A1").Select End If Next Sh Application.CutCopyMode = False End Sub

Just insert it in a module in Visual Basic, and then go to View . . . Macros . . . View Macros and Run 'Paste'. This macro was posted by Andrew Poulsom to this site:

http://www.mrexcel.com/forum/excel-questions/63213-visual-basic-applications-code-copy-worksheets-paste-values.html

Excel Macro to Unfilter Data on Multiple Worksheets

See this macro which will unfilter data on multiple worksheets.

Sub Unfilter() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.AutoFilterMode = False Next ws End Sub

Just insert in a module and run it. It can be a useful tool in processing data for production.

Excel AutoFill Anomaly

Today I experienced a problem with Excel that I had never encountered before. After using the Auto Fill feature (either by pressing CTRL + D or pulling the small handle at the bottom right edge of a cell) about a billion times to pull down formulas to below cells, suddenly today the formulas failed to indicate the values entered in succeeding referenced cells, and instead showed the value from the cell that was the original reference point. So in the example below, you see how in cell C5 the formula adding cells A5 and B5 doesn't give the result 476, but instead shows the value for the formula in C2, where I begin the selection before pressing CTRL + D.

For a while this problem drove me nuts. What the hell was I doing wrong? Did the cells need to be reformatted?

The solution to the problem was to go to File . . . Options . . . Formulas and look in the Calculations Options section. The anomaly arises when the Workbook Calculation is set to Manual, rather than Automatic. When it's reset back to Automatic . . .

. . . the spreadsheet should immediately update and the formulas will be displayed correctly.