Compile Error on Private Sub (Sub or Function not defined), when executing Macro

[stevobo]

Hi all,

I have got 3 macros in place. 1 private sub on a worksheet to make a calculation when a cell value changes as per below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$F$6" Then
        Call Herberekenen_Resources
    End If
End Sub

This 1st macro calls the following 2nd macro to recalculate certain fields as per below:

Sub Herberekenen_Resources()
'
    Application.ScreenUpdating = False
    Sheets("Master").Select
    Range("CO2").Select
    Selection.Copy
    Range("CO5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("CO5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("CO3").Select
    Application.CutCopyMode = False
   
    ThisWorkbook.RefreshAll
    Sheets("Resource Dashboard").Select
    Range("B2").Select
    Application.ScreenUpdating = True
   
End Sub

The 3rd macro copies a worksheet to another workbook, as per below:

Sub ResourceSheet_Voorbereiden()
'
' ResourceSheet_Voorbereiden
'
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
   
    Range("B6").Select
    Selection.Copy
    Range("b7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Resource dashboard").Select
    Sheets("Resource dashboard").Copy
    Range("F5").Select
    Selection.Hyperlinks.Delete
    Range("F5").Font.Size = 9
    Range("B6").Select
    Selection.Clear
    Range("F5:J250").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Shapes.Range(Array("Gebruikt in ConOps 1", "Cluster 1", "Categorie 1", "CONOPS 1", "Beheerder 1", "Type resource 1", "Knop 1", "Knop 2")).Select
    Selection.Delete
   
    Sheets("Resource dashboard").Select
    Sheets("Resource dashboard").Copy After:=Sheets(1)
    Range("A1").Select
    Worksheets("Resource dashboard (2)").Visible = False
   
    Sheets("Resource dashboard").Select
    Range("F10:J250").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=NIET(F10='Resource dashboard (2)'!F10)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16777024
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13421823
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    Rows("3:4").Select
    Selection.EntireRow.Hidden = True

    Range("A1").Select
    Application.DisplayAlerts = True
   
    Dim Naam As String
    Naam = "BCM Resource Overview " & Range("b7") & ".xlsx"
   
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Environ("userprofile") & "\Downloads\" & Naam, FileFormat:=51, CreateBackup:=False
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
   
End Sub

When running the 3rd macro I get an error that brings me to the private sub and indicates that there is a compilation error: "Sub or Function is not defined.

Can anyone help me resolve this error?

Many thanks.

 

[RoryA]

Which module is the Herberekenen_Resources code in?

 

[stevobo]

Which module is the Herberekenen_Resources code in?

It's in Module 1. The Private Sub is in Blad10(Resource Dashboard). Could that be causing the issue?

Many thanks for your help.

 

[RoryA]

Ah. The problem is that you have copied that sheet to a new workbook, then changed it, so its worksheet_change code tries to call a routine that is not in that workbook. You should disable events to stop that happening. Add Application.Enableevents = False to the start of the ResourceSheet_Voorbereiden, then reset it back to True at the end.

 

[stevobo]

Ah. The problem is that you have copied that sheet to a new workbook, then changed it, so its worksheet_change code tries to call a routine that is not in that workbook. You should disable events to stop that happening. Add Application.Enableevents = False to the start of the ResourceSheet_Voorbereiden, then reset it back to True at the end.

That's perfect! Problem solved. Thank you so much.

 

[RoryA]

Glad to help.