gkisystems
Board Regular
- Joined
- Apr 20, 2012
- Messages
- 76
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
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