asked on

macro to remove formulas which references a particular sheet

Dear Experts,

I would like a macro that will evaluate all sheets within the workbook except the "Any Term" sheet and replace any formula that references to the "Any Term" sheet with the value (copy-paste special values).

Many thanks for your advice

Chris Bottomley

How about:

Chris

Sub noForm()
Dim ws As Worksheet

    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) <> "any term" Then
            ws.UsedRange.Copy
            ws.UsedRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub

Open in new window

SOLUTION

Chris Bottomley

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Bottomley

Ah! I missed that distinction - glad someone's synapses are firing correctly.

Chris

SOLUTION

byronwall

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

>This is a more direct approach. For what it's worth.

While its shorter code it will be much slower. Plus it should be a more specific search to ensure it is a sheet name

For loops can and should be avoided

Dave

byronwall

Maybe we will get a sheet to try them out on. I doubt the Find approach is significantly faster. Also your notion that For loops should be avoided is just incorrect. Any type of loop can be used to mimic any other type of loop: the application determines the correctness.

Given the nature of most spreadsheets, I doubt that searching explicitly for just the sheet name in formulas will yield false positives. If it does, we could improve the search term by adding the !.

And there is something to be said for shorter code if you ever have to come back to it (or worse, someone else has to). This is especially true if the shorter code does not rely on regular expressions and the unintuitive results of a Do loop iterating a Find and Union.

Dave

> I I doubt the Find approach is significantly faster. Also your notion that For loops should be avoided is just incorrect

You do well then to up your experience levels before posting in areas you are unfamiliar with. The other prudent approach would be to test your theories before posting

TommySzalapski

byron, you are partially correct in that for loops are often useful tools. However, Excel vba is a scripted language (not a compiled one) so the more you can use the built-in compiled functions (like 'Find'), the faster your code will go. For loops do not get unrolled or optimized in any way at all in VBA.

If you are coding in a compiled language like C, C++, java, even VB.NET, etc. then use for loops all day long. But in VBA, .vbs, MATLAB, etc. use built-in functions whenever possible. They are, in fact, significantly faster.

Dave, come on, he registered, like, maybe four hours ago. Go easy on him. :)

hendrkle

ASKER

Thank you all for your great input, I found the solution from brettdj best fit for my purpose and am using i now, thank you!