Macro keeps giving debug error

[kingspur06]

I have several tabs which I need to copy paste into a single tab to allow a pivot table to be created on the consolidated information.
I created it by recording it but I constantly get a debug error "Code Execution has been interrupted"
It also doesn't seem to stop at the same place each time.

My macro looks like this

Sub ConsolData()
'
' ConsolData Macro
'
' Keyboard Shortcut: Ctrl+a
'
    Sheets("UK").Select
    Columns("A:BF").Select
    Selection.Copy
    Sheets("Consolidated Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A2000").Select
    
    Sheets("Spain").Select
    Range("A2:BF1000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Consolidated Data").Select
    ActiveSheet.Paste
    Range("A2999").Select
    
    Sheets("Europe").Select
    Range("A2:BF1000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Consolidated Data").Select
    ActiveSheet.Paste
    Range("A3998").Select
    
    Sheets("USA").Select
    Range("A2:BF1000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Consolidated Data").Select
    ActiveSheet.Paste
    Range("A4997").Select
    
    Sheets("Archive 22-23").Select
    Range("A2:BF1150").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Consolidated Data").Select
    ActiveSheet.Paste
    
    Sheets("Summary").Select
End Sub

I suspect the issue may be being caused by another macro that I have running which highlights cells whenever a constant value is entered after you have entered that sheet. When I step through my original macro, it always runs through the 2nd macro when going into each sheet.

The 2nd macro is

Option Explicit
Private Sub Worksheet_Activate()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.Range("o2:aa2000").SpecialCells(xlCellTypeConstants)
cell.Interior.ColorIndex = 17
Next
End Sub

Does anyone have any suggestions, either how to consolidate my sheets into one sheet more elegantly, or how to prevent the bugging?
many thanks
Mark

 

[6StringJazzer]

The message "Code Execution has been interrupted" generally means that you have hit Break forcing an interruption to execution. I have seen this happen without hitting break but never been able to reproduce it. There may be some buggy interaction with other processes. It is very strange that you get this repeatedly.

I agree with HaHoBe that you should try a reboot.

 

[HaHoBe]

Hi Mark,

what came to my mind is how you have assigned the shortcut for the macro: if you have done so using the Macro Options window alone (which would make the macro available in every open workbook and would mean to include the name of the workbook in order make the code available only in one workbook) or via VBA in ThisWorkbook like

Private Sub Workbook_Open()
  Application.OnKey "^{A}", "ConsolData"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Application.OnKey "^{A}", ""
End Sub

Private Sub Workbook_Activate()
  Application.OnKey "^{A}", "ConsolData"
End Sub

Private Sub Workbook_Deactivate()
  Application.OnKey "^{A}", ""
End Sub

On my system (Windows11, Excel2019 Professional) I was not able to assign the shortcut CTRL+a like indicated in your comment (command should select all cells in the sheet) but have assigned CTRL+SHIFT+a (or as shown above CTRL+A). I deactivated the shortcut on the Macro Options window and after switching workbooks the macro could only work in one workbook, the one holding the commands.

This certainly does not explain why you have faced the error - I'm sorry I can't offer a solution to avoid this for the future.

Ciao,
Holger

 

[kingspur06]

Hi everyone - many thanks to you all for your responses. It is now working today. I don't know whether it was the full reboot that I did. I also noticed I had accidently pulled in references to an external source which I have now removed. Again not sure if that had anything to do with it.....but it all works great.
Thank you once again!!!