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

[RockandGrohl]

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"

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!

 

[Tim_Excel_]

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).

'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:

'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

 

[RockandGrohl]

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.

 

[Tim_Excel_]

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?

 

[RockandGrohl]

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.

 

[Tim_Excel_]

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.

 

[RockandGrohl]

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.

 

[RockandGrohl]

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!

 

[Tim_Excel_]

Glad to have helped!