Agfdhyk

I'm using this code below to convert formula to cells, which works fine in a single sheet. But the problem is when I need to convert all selected cells which are in different sheets to their value, this code doesn't do it.

This is how I am selecting the cells in Excel:
first I select the cells in one sheet, than I go down to the tabs right-click and select specific sheets, which in Excel selects the corresponding cells in every selected sheet.

So any tips on how I can change this code to make it work across different sheets?

Sub formulaToValues()

    If Selection.Cells.Count = 1 Then

        Selection.Value = Selection.Value

        Selection.Cells.Interior.ColorIndex = 0

        Selection.Cells.Font.Color = vbBlack

    Else

        For Each cel In Selection.Cells

            cel.Value = cel.Value

            Selection.Cells.Interior.ColorIndex = 0

            Selection.Cells.Font.Color = vbBlack

        Next cel

    End If

End Sub

You should be able to just grab the address of the selection, then add that to each worksheet's range

Sub formulaToValues()



    Dim celAddr As String

    celAddr = Selection.Address



    Dim ws As Worksheet

    For Each ws In ActiveWindow.SelectedSheets

        With ws.Range(celAddr)

            .Value = .Value

            .Interior.ColorIndex = 0

            .Font.Color = vbBlack

        End With

    Next ws



End Sub

You are attempting to write to a 3D cell collection. An interesting problem i haven't seen before. I gave it a shot.

The below code works for me. I have simply added an extra loop to search through any other sheets. Note: it is good practice to always declare your variables.

Answer1: This cycles through every sheet in the workbook

Sub formulaToValues()

Dim cel As Range

Dim ws As Worksheet

If Selection.Cells.Count = 1 Then

    Selection.Value = Selection.Value

    Selection.Cells.Interior.ColorIndex = 0

    Selection.Cells.Font.Color = vbBlack

Else

    For Each ws In ThisWorkbook.Worksheets

        For Each cel In Selection.Cells

            ws.Range(cel.Address).Value = 2 'cel.Value

            Selection.Cells.Interior.ColorIndex = 0

            Selection.Cells.Font.Color = vbBlack

        Next cel

    Next ws

End If



End Sub

Answer2: With this one it only goes throug the selected sheets

Sub formulaToValues()

Dim cel As Range

Dim ws As Worksheet

If Selection.Cells.Count = 1 Then

    Selection.Value = Selection.Value

    Selection.Cells.Interior.ColorIndex = 0

    Selection.Cells.Font.Color = vbBlack

Else

    For Each ws In ThisWorkbook.Windows(1).SelectedSheets

        For Each cel In Selection.Cells

            ws.Range(cel.Address).Value = 2 'cel.Value

            Selection.Cells.Interior.ColorIndex = 0

            Selection.Cells.Font.Color = vbBlack

        Next cel

    Next ws

End If



End Sub

Thanks alot guys, this got answered pretty quickly.
I am placing my macros in personal so I ended if with this

Sub formulaToValues3()

Dim cel As Range

Dim ws As Worksheet

If Selection.Cells.Count = 1 Then

Selection.Value = Selection.Value

Selection.Cells.Interior.ColorIndex = 0

Selection.Cells.Font.Color = vbBlack

Else

For Each ws In ActiveWorkbook.Windows(1).SelectedSheets

    For Each cel In Selection.Cells

        ws.Range(cel.Address).Value = ws.Range(cel.Address).Value 'cel.Value

        Selection.Cells.Interior.ColorIndex = 0

        Selection.Cells.Font.Color = vbBlack

    Next cel

Next ws

End If



End Sub