Macro change formulas to values

[ste33uka]

Hi i have a workbook with sheets names 1,2,3,4,5,67,8,9,10

Would anyone have a macro that would change the cells B2 to C10 and G2 to G20 from formulas into values ?
Thanks

 

[davesexcel]

B2 to C10 and G2 to G20

Range("B2:C10").Value = Range("B2:C10").Value Range("G2:G20").Value = Range("G2:G20").Value

 

[dmt32]

Try,

Sub MakeValue()
    Dim i As Integer
    Dim cell As Range
    For i = 1 To 10
     For Each cell In Worksheets(CStr(i)).Range("B2:C10,G2:G20").Cells
        cell.Value = cell.Value
     Next cell
    Next i
End Sub

Dave

 

[ste33uka]

Try,

Sub MakeValue()
    Dim i As Integer
    Dim cell As Range
    For i = 1 To 10
     For Each cell In Worksheets(CStr(i)).Range("B2:C10,G2:G20").Cells
        cell.Value = cell.Value
     Next cell
    Next i
End Sub

Dave

Thanks alot, thats perfect

 

[dmt32]

Glad it worked

untested but another approach maybe

Sub MakeValue()
    Dim i As Integer
    Dim Areas As Range
    For i = 1 To 10
     For Each Area In Worksheets(CStr(i)).Range("B2:C10,G2:G20").Areas
        Area.Value = Area.Value
     Next Area
    Next i
End Sub

Dave