Stepping through this macro works perfectly, but I get a runtime error every time I run it.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
801
Office Version
  1. 365
Platform
  1. Windows
Hello all, the "problem area" is highlighted in Bold.

We've tried extensively to fix this including aliasing all sheets and variants of "ActiveWorkbook.Activate"

Code:
Sub Produce_Report()'
' Produce_Report Macro
'


'
Application.ScreenUpdating = False
Application.Calculation = xlManual




Dim F As Worksheet
Dim PM As Worksheet
Dim MM As Worksheet
Dim CM As Worksheet




    Workbooks.Add
    ActiveWorkbook.SaveAs ("\\chw-dc03\Company\Sales\Chris\Enquiry Conversions\Report\EC Report " & Format(Now(), "DD-MM-YYYY hh mm ss AMPM") & ".xlsx")
    
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Front"
    Set F = Worksheets("Front")
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "Product Matrix"
    Set PM = Worksheets("Product Matrix")
    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "Marketing Matrix"
    Set MM = Worksheets("Marketing Matrix")
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet4").Select
    Sheets("Sheet4").Name = "Customer Matrix"
    Set CM = Worksheets("Customer Matrix")
    
    
    Windows("Enquiry Conversions.xlsm").Activate
    Worksheets("Front").Activate
    Range("A1:U29").Select
    Selection.Copy
    
F.Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.Zoom = 90


ThisWorkbook.Activate
    Sheets("Product Matrix").Select
    Range("A1:AB28").Select
    Application.CutCopyMode = False
    Selection.Copy
PM.Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.Zoom = 90
    
ThisWorkbook.Activate
    Sheets("Marketing Matrix").Select
    Range("A1:R140").Select
    Application.CutCopyMode = False
    Selection.Copy


MM.Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.Zoom = 90
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
ThisWorkbook.Activate
    Sheets("Customer Matrix").Select
    Range("A1:AD35").Select
    Application.CutCopyMode = False
    Selection.Copy


CM.Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.Zoom = 80
    Range("A1").Select


F.Select
    With F.Tab
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = -0.249977111117893
    End With
    Range("A1").Select
PM.Select
    With PM.Tab
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
    End With
    Range("A1").Select
MM.Select
    With MM.Tab
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
    End With
    Range("A1").Select
CM.Select
    With CM.Tab
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
    End With
F.Select


Application.Calculation = xlAutomatic
Application.ScreenUpdating = True


End Sub


If I step through the Macro, it does everything exactly as it should. If I click play or run it from a button it immediately errors and debugs.

Any help would be grand!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Firstly, nothing is bold so I am unsure what is throwing the error.

Secondly, activating ranges/sheets/workbooks is poor practice and most likely in this case the result of recording a macro (which is great to indicate how to code something, but I don't advise on using the code is produces).

Code:
'For example
Range("A1:U29").Select
Selection.Copy
'Above code has to become:
Range("A1:U29").Copy

Selecting and activating can throw all sorts of errors.. See if this helps.


Thirdly, make sure you reference to the correct workbook and worksheet. If you don't, like in your code, a reference like Range("...") can refer to whatever workbook/sheet you clicked on and is now activated.
Better practice:
Code:
'this is good
Workbooks("name.xls").Sheets(1).Range("A1:U29").Copy

'this is better, because you can use the With statement for as many lines as you want, as long as they're within this statement
With Workbooks("name.xls").Sheets(1)
.Range("A1:U29").Copy
End With
 
Last edited:
Upvote 0
Apologies, I re-copied the code and lost the formatting I wrote.

It's after the "Activeworkbook.saveAs" right at the top, the next line after gives a debug error.
 
Upvote 0
I'd advise you to edit the code the way I mentioned in my previous comment.

However, that doesn't seem to be the cause of the error. What is the error message?
 
Upvote 0
Hi Tim,

It's "Code Execution has been interrupted"

When I hit debug, "Sheets("Seet1").Select is highlighted.


When the new workbook is added, it's autosaved as EC Report with the date and time. This workbook has 3 sheets.

The next section of the code renames and aliases these sheets. When stepping through with F8 it works absolutely fine and as you say, the bad practice part of the code still produces the correct result.

Thanks.
 
Upvote 0
Oh no... This sounds very similar to an Excel bug I have encountered not too long ago

This "Error" is what you'd see when you would manually stop the code. It isn't a true error message. Excel can be quirky, there have been times during which I had to copy the entire code to another workbook to get rid of a very persistent bug!

Fortunately, the solution in this thread on StackOverflow did the trick for me. Hope it helps.
 
Last edited:
Upvote 0
Cheers Tim I'll take a look. Now that you mention it, it does stop spontaneously and often will get near the end, however 9/10 times it stops at the start where I mentioned. Let me have a look now.
 
Upvote 0
Oh no... This sounds very similar to an Excel bug I have encountered not too long ago

This "Error" is what you'd see when you would manually stop the code. It isn't a true error message. Excel can be quirky, there have been times during which I had to copy the entire code to another workbook to get rid of a very persistent bug!

Fortunately, the solution in this thread on StackOverflow did the trick for me. Hope it helps.


Haha! Tim, that was genius. Worked straight away. How very CURIOUS!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top