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 ...
developer
excel
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP

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.

    • RGee77's avatar
      RGee77
      Copper Contributor
      Oct 05, 2023

      HansVogelaar 

       

      And now a this section is now highlighted: 

      ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
      PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Table1", Version _
      :=xlPivotTableVersion15)

       

      (Sorry, I'm not really knowledgeable with the coding part of macros...)

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 05, 2023

        RGee77 

        This is going to take too long.

        Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Resources