Selecting Specialcells in all sheets

[davper]

I am having trouble looping through the sheets in my workbook and selecting SpecialCells.

When I run this code, it only processes the activesheet.

Sub Testz()
Dim Page As Worksheet
    For Each Page In ActiveWorkbook.Worksheets
        Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Next
End Sub

My first try was

Page.SpecialCells(xlCellTypeFormulas, 23).Select

But that just gave me a 'Selection method failure.

I know it is looping through all sheets because I put in a debug.print to give me each sheet name.

I have tried using xlNumbers instead of 23. I have left it blank.

This is a very simple code for demonstration. My actual code is going to process each cell individually depending on further criteria.

Please and Thank You

 

[davper]

This is the other code that I tried that gives me a 'Selection Method' error:

Sub Testz()
Dim Page As Worksheet
    For Each Page In ActiveWorkbook.Worksheets
        'Selection.SpecialCells(xlCellTypeFormulas, 23).Select
        Page.UsedRange.SpecialCells(xlCellTypeFormulas).Select
    Next
End Sub

 

[JSA715]

if you are hell bent on selecting the cell (which 99% of the time is superfluous) then try adding page.select before the selection line.

-Justin

 

[VoG]

Try

Sub Testz()
Dim Page As Worksheet
    For Each Page In ActiveWorkbook.Worksheets
        Page.Select
        SpecialCells(xlCellTypeFormulas, 23).Select
    Next
End Sub

 

[davper]

I am not hell bent on selecting. By using select while testing, I can get an immediate visual clue that it worked.

page.select worked for me. Thank you

here is my actual code for the curious

For Each Page In ActiveWorkbook.Worksheets
    Page.Select
    Set rngRange = Page.UsedRange.SpecialCells(xlCellTypeFormulas)
    For Each rngCell In rngRange
        If InStr(rngCell.Formula, "AMOUNT") Then
            rngCell.Formula = rngCell.Value
        End If
        If InStr(rngCell.Formula, "[") Then
            rngCell.Formula = rngCell.Value
        End If
    Next
Next

 

[VoG]

That will work without Page.Select

 

[JSA715]

I don't think you'll need the page.select on that. Did you try that particular code w/o the select?

 

[JSA715]

That will work without Page.Select

I would like to think that I hit the reply button before you, but since my reply was longer, it took me longer to write. However, that is probably not the case.

 

[davper]

Without page.select, it only runs the code on the activesheet.

 

[VoG]

You surprise me. What about

For Each Page In ActiveWorkbook.Worksheets
    With Page
        Set rngRange = .UsedRange.SpecialCells(xlCellTypeFormulas)
        For Each rngCell In rngRange
            If InStr(rngCell.Formula, "AMOUNT") Then
                rngCell.Formula = rngCell.Value
            End If
            If InStr(rngCell.Formula, "[") Then
                rngCell.Formula = rngCell.Value
            End If
        Next
    End With
Next