Forum Discussion

RGee77's avatar
RGee77
Copper Contributor
Oct 05, 2023

Macro no longer working

I have been using a spreadsheet that friend made a few years ago that has a macro embedded in it. This past week, Excel has updated to a newer version and now I am receiving a Run-Time error message and am unable to run the macro. 

 

Here is the Runtime Error:

 

When I click on "Debug", I'm brought to the Macro code and this part is highlighted:

(If the image is too small, the part highlighted reads "Selection.PasteSpecial Paste:=xlPasteValues, Operation:xlNone, SkipBlanks _ :=False, Transpose:=False")

 

I'm assuming that something in this part of the code is no longer compatible with the most recent Excel update. Any ideas on how to tweak this code so that I can run the macro? 

 

Thanks in advance.  

 

developer
Excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 05, 2023

    RGee77 

    I have no idea why the error occurs, but try replacing the lines

        Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    with

        With ActiveSheet.UsedRange
        .Value = .Value
    End With
    • RGee77's avatar
      RGee77
      Copper Contributor
      Oct 05, 2023
      Thanks for the response- I replaced those lines and got a different Run-time error: '1004" Method 'Range' of object'_Global" failed.

      And when I click on "Debug" the next line "Range("Table1[[#Headers],[Record ID'#]]").Select" is highlighted.

      Any ideas??



      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 05, 2023

        RGee77 

        Since you activate another sheet immediately after that, the line

         

        Range("Table1[[#Headers],[Record ID'#]]").Select

         

        isn't needed. I'd either remove it or turn it into a comment by inserting an apostrophe (single quote) before it.

Resources