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:
This 1st macro calls the following 2nd macro to recalculate certain fields as per below:
The 3rd macro copies a worksheet to another workbook, as per below:
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.
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:
VBA Code:
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:
VBA Code:
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:
VBA Code:
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.
Last edited by a moderator: