Macro error help

[anilag1bowl]

Hey guys, I'm a savvy excel user, but not huge in the programming/macro side. I keep getting this error on a simple button to turn formulas into values, and save the workbook as a static.

Error: Run-time error '1004': Select method of Button class failed

Dropping the code in and the error highlighted by VisualBasic, can I please get some help how to fix? The Macro does not work.

Thanks!

Anilag

 

[ParamRay]

.
.

Not sure why that line isn't working for you; it appears to work ok on my PC.


Try something like the following instead:

Sub AddButton()

    Dim Btn As Button
    Dim Rng As Range
    
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    
    Select Case TypeName(Selection)
        Case Is = "Range": Set Rng = Selection.Item(1).Resize(3, 3)
        Case Else: Set Rng = ActiveSheet.Range("B2:D4")
    End Select
    
    With Rng
        Set Btn = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
    End With
    
    With Btn
        .Caption = "Paste Values and Save"
        .OnAction = "PasteValuesAndSave"
    End With

End Sub


Sub PasteValuesAndSave()

    Dim Sel As Object
    Dim FRang As Range
    Dim FArea As Range
    
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    
    On Error Resume Next
    Set FRang = ActiveSheet.Cells.SpecialCells( _
        xlCellTypeFormulas, _
        xlErrors + xlLogical + xlNumbers + xlTextValues)
    On Error GoTo 0
    
    If Not FRang Is Nothing Then
        Set Sel = Selection
        Application.ScreenUpdating = False
    
        For Each FArea In FRang.Areas
            With FArea
                .Copy
                .PasteSpecial Paste:=xlPasteValues
            End With
        Next FArea
        
        Sel.Select
        With Application
            .CutCopyMode = False
            .ScreenUpdating = True
        End With
    End If
    
    ActiveWorkbook.Save

End Sub