on open

[izzyq]

Is it possible for me to manipulate this code such that if the filename is OPEX Variance Analysis rev.2.xls it will run the macro, otherwise, nothing will happen.

Here is the code that I have so far.

Option Explicit



Private Sub Workbook_Open()
Dim filename As String
If filename = "Y:IQ38316DataExcelOPEX-CAPEX Input SheetsOPEX Variance Analysis rev.2.xls" Then

'set range you want to clear
Worksheets("Variance Analysis").Range("K30:T53").ClearContents

'returns OrgCode Values back to default "*" value
Worksheets("Variance Analysis").Range("C8:C10").Value = "*"

'returns month back to default of "01-January"
Worksheets("Variance Analysis").Range("B1").Value = "'01"

End If


End Sub

[Colo]

Hi. This is not good way.
Please try this, while you wait for other methods of other respondents.

'Please copy this into a standard module in PERSONAL.XLS
Sub BookNameCheck()
Dim filename As String, wb As Workbook
filename = "OPEX Variance Analysis rev.2.xls"
For Each wb In Workbooks
If wb.Name = filename Then
'set range you want to clear
Worksheets("Variance Analysis").Range("K30:T53").ClearContents
'returns OrgCode Values back to default "*" value
Worksheets("Variance Analysis").Range("C8:C10").Value = "*"
'returns month back to default of "01-January"
Worksheets("Variance Analysis").Range("B1").Value = "'01"
End If
Next
End Sub

'Copy this into ThisWorkbook module in Each Book
Private Sub Workbook_Open()
Application.Run "PERSONAL.XLS!BookNameCheck"
End Sub