RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 801
- Office Version
- 365
- Platform
- 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"
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!
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!