You're not telling it which one of the three sheets to use.
Only one of those sheets should be visible and that's the one you want.
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim xFileName As String
Dim L5part As String, H17part As String
Dim ws As Worksheet
If ThisWorkbook.Worksheets("Report template - Advanced").Visible = True Then
Set ws = ThisWorkbook.Worksheets("Report template - Advanced")
ElseIf ThisWorkbook.Worksheets("Report template - NextGen").Visible = True Then
Set ws = ThisWorkbook.Worksheets("Report template - NextGen")
Else
Set ws = ThisWorkbook.Worksheets("Report template - All Future")
End If
L5part = ws.Range("L5").Value
L5part = Replace(Replace(L5part, " / ", "/"), "/", "_")
H17part = ws.Range("H17").Value
H17part = Replace(H17part, "/", "_")
If SaveAsUI <> False Then
Cancel = True
xFileName = Application.GetSaveAsFilename(H17part & " Report" & ws.Range("A1").Value & L5part, _
"Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save report as")
If xFileName <> "False" Then
Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
End If
End If
End Sub
Hope that works.