How do I use VBA to activate a window - but a different window name each time?

[gkisystems]

I have a Macro I recorded with the recorder. It opens a new workbook and copy-paste special values the data from the original workbook into the new workbook.

The general filename of the original workbook is "Broker Report Template.xlsm." However, I want to have several original workbooks and name them by date. For example:

2012.08.14 - Broker Report Template.xlsm
2012.08.15 - Broker Report Template.xlsm
2012.08.16 - Broker Report Template.xlsm

The problem is that when I add the date prefix to the filename, the macro results in this error:
Run-time error '9': Subscript out of range

And then my options are end, debut, and help. When I change the filename back to just Broker Report Template.xlsm there is no such error.

How do I address this? I don't really care about the error, so if there is any code I can add to my macro to ignore this error so the error message does not pop-up, it would be greatly appreciated!

Here is the macro VBA as it exists now:

Option Explicit
Sub Export()
'
' Export Macro
'
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("A1").Select
Windows("Broker Report Template.xlsm").Activate
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Broker[All]", xlLabelOnly, _
True
Range("A6").Select
End Sub

 

[AlphaFrog]

Sub Export()
'
' Export Macro
'
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("A1").Select
ThisWorkbook.Activate
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Broker[All]", xlLabelOnly, _
True
Range("A6").Select
End Sub

ThisWorkbook will reference the workbook that the macro code is within regardless of its name.

 

[gkisystems]

Sub Export()
'
' Export Macro
'
Cells.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Cells.EntireColumn.AutoFit
Range("A1").Select
ThisWorkbook.Activate
Application.CutCopyMode = False
ActiveSheet.PivotTables("PivotTable1").PivotSelect "Broker[All]", xlLabelOnly, _
True
Range("A6").Select
End Sub

ThisWorkbook will reference the workbook that the macro code is within regardless of its name.

Perfect - thanks!!!