kingspur06
Board Regular
- Joined
- Apr 24, 2007
- Messages
- 52
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
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
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
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
VBA Code:
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
VBA Code:
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