모든 시트 수식에서 값으로 변환VBA

http://www.rondebruin.nl/win/s9/win015.htm

 

 

Change formulas to values

Manual

To convert all cells on a worksheet to values we must select all cells first.

Excel 97-2002:
Use the shortcut Ctrl-A to select all cells.

Excel 2003:
If the worksheet contains data, CTRL+A selects the current region.
Pressing CTRL+A a second time selects the entire worksheet.

Excel 2007-2013:
If the worksheet contains data, CTRL+A selects the current region.
Pressing CTRL+A a second time selects the current region and its summary rows.
Pressing CTRL+A a third time selects the entire worksheet.

This is working in all Excel versions:
Click on the space above the first row header and on the left of the first column header.



Note: you can also select a range instead of the whole sheet

Excel 97-2003:
Edit>Copy or CTRL+C
Edit>Paste Special
Check "Values"
OK
Use Esc to clear the clipboard
Press Ctrl+Home to go to A1

Excel 2007-2013:
Home tab>Clipboard group>Copy or CTRL+C
Home tab>Clipboard group>Arrow on Paste button
Choose "Paste Values" in the menu
Use Esc to clear the clipboard
Press Ctrl Home to go to A1

All Excel versions:
Right click on the selection and choose Copy
Right click on the selection and choose Paste Special
Check "Values"
OK
Use Esc to clear the clipboard
Press Ctrl Home to go to A1

 

Macro examples

Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
Where do I paste the code that I find on the internet

The first macro of each example use PasteSpecial xlPasteValues and the second the value property(both do the same). You must remove the worksheet protection before you can use the macro's.

Warning: Value property problem
If there are constants in the .UsedRange that have character by character formatting, the .Value = .Value will change the formatting to match the first character in that cell.

Change all the cells in all worksheets to values

You can remove the sh.Select line and .Cells(1).Select line if you want.
I add it because I not like the selection of the usedrange in every sheet after you use PasteSpecial.

Sub All_Cells_In_All_WorkSheets_1()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        sh.Select
        With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next sh
End Sub


Sub All_Cells_In_All_WorkSheets_2()
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        With sh.UsedRange
            .Value = .Value
        End With
    Next sh
End Sub

Change all the cells in the ActiveSheet to values

Sub All_Cells_In_Active_WorkSheet_1()
    With ActiveSheet.UsedRange
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False
End Sub


Sub All_Cells_In_Active_WorkSheet_2()
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
End Sub

Change all the cells in the CurrentRegion to values

Use the the Shortcut Ctrl * with the A1 selected to see the CurrentRegion.
It is a range with data bounded by a empty row/column.

Sub CurrentRegion_Example_1()
    With Range("A1").CurrentRegion
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False
End Sub


Sub CurrentRegion_Example_2()
    With Range("A1").CurrentRegion
        .Value = .Value
    End With
End Sub

Change all the cells in a range to values

Sub Range_Example_1()
    With Range("A5:D100")
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False
End Sub


Sub Range_Example_2()
    With Range("A5:D100")
        .Value = .Value
    End With
End Sub

Change all the cells in a range with one or more areas to values

Sub Range_With_One_Or_More_Areas_Example_1()
    Dim smallrng As Range
    For Each smallrng In Range("A1:C10,E12:G17").Areas

        'If you want to run the code on a selection with one or more
        'areas you can use For Each smallrng In Selection.Areas

        With smallrng
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next smallrng
End Sub


Sub Range_With_One_Or_More_Areas_Example_2()
    Dim smallrng As Range
    For Each smallrng In Range("A1:C10,E12:G17").Areas

        'If you want to run the code on a selection with one or more
        'areas you can useFor Each smallrng In Selection.Areas

        With smallrng
            .Value = .Value
        End With
    Next smallrng
End Sub

Break only formula links to other Excel workbooks

Sub Break_Links_To_other_Excel_Workbooks()
'This example convert formulas that point to another Excel workbook to values
'It will not convert other Excel formulas to values.
'Note that BreakLink is added in Excel 2002
    Dim WorkbookLinks As Variant
    Dim wb As Workbook
    Dim i As Long

    Set wb = ActiveWorkbook

    WorkbookLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
    If IsArray(WorkbookLinks) Then
        For i = LBound(WorkbookLinks) To UBound(WorkbookLinks)
            wb.BreakLink _
                    Name:=WorkbookLinks(i), _
                    Type:=xlLinkTypeExcelLinks
        Next i
    Else
        MsgBox "No Links to other workbooks"
    End If
End Sub