I have a macro that at the end saves the file with the name and a date that is listed in a certain column/row.
It runs perfectly fine in 2003 or 2007 excel. However, when I run it in excel 2013 the date saves like this:
Monthly Client Total Performance #####.xlsx or just 2015.xlsx
I have tried the macro multiple ways and the only way I can really get the date to save is when I do a ActiveWorkbook.SaveAs Filename:= _ "Q:\Performance\Quarterly Reporting -- Performance Comparison Analysis\Total Portfolio Performance Comparison\Monthly Client Total Performance " &Range("A5").Text, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
However, the problem is when I do above the file's format and macros all save. I had previously written below:
Dim DestBook As Workbook, SrcBook As Workbook
Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook
On Error Resume Next
Set DestBook = Workbooks.Open("C:\My Documents\Anitha.xls") ' Ignore this. Used for testing
If Err.Number = 1004 Then
Set DestBook = Workbooks.Add
SrcBook.Worksheets(1).Range("A1:P1500").Copy
DestBook.Worksheets(1).Range("A1").PasteSpecial
Application.CutCopyMode = False
Dim DateSelection As String
DateSelection = Sheets("Sheet1").Range("A5").Text
'MsgBox DateSelection ' Used for testing
DestBook.SaveAs Filename:= _
"Q:\Performance\Quarterly Reporting -- Performance Comparison Analysis\Total Portfolio Performance Comparison\Monthly Client Total Performance " & Range("A5") & ".xlsx", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else ' This is a failsafe code, but will probably never be used when macro is run
SrcBook.Worksheets(1).Range("A1:P1500").Copy
DestBook.Worksheets(1).Range("A1").PasteSpecial
Application.CutCopyMode = False
DestBook.save
DestBook.Close
End If
On Error GoTo 0 ' If an error is found in the code, return 0 and end macro
Set DestBook = Nothing
Set SrcBook = Nothing
Have I missed something completely???
It runs perfectly fine in 2003 or 2007 excel. However, when I run it in excel 2013 the date saves like this:
Monthly Client Total Performance #####.xlsx or just 2015.xlsx
I have tried the macro multiple ways and the only way I can really get the date to save is when I do a ActiveWorkbook.SaveAs Filename:= _ "Q:\Performance\Quarterly Reporting -- Performance Comparison Analysis\Total Portfolio Performance Comparison\Monthly Client Total Performance " &Range("A5").Text, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
However, the problem is when I do above the file's format and macros all save. I had previously written below:
Dim DestBook As Workbook, SrcBook As Workbook
Application.ScreenUpdating = False
Set SrcBook = ThisWorkbook
On Error Resume Next
Set DestBook = Workbooks.Open("C:\My Documents\Anitha.xls") ' Ignore this. Used for testing
If Err.Number = 1004 Then
Set DestBook = Workbooks.Add
SrcBook.Worksheets(1).Range("A1:P1500").Copy
DestBook.Worksheets(1).Range("A1").PasteSpecial
Application.CutCopyMode = False
Dim DateSelection As String
DateSelection = Sheets("Sheet1").Range("A5").Text
'MsgBox DateSelection ' Used for testing
DestBook.SaveAs Filename:= _
"Q:\Performance\Quarterly Reporting -- Performance Comparison Analysis\Total Portfolio Performance Comparison\Monthly Client Total Performance " & Range("A5") & ".xlsx", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Else ' This is a failsafe code, but will probably never be used when macro is run
SrcBook.Worksheets(1).Range("A1:P1500").Copy
DestBook.Worksheets(1).Range("A1").PasteSpecial
Application.CutCopyMode = False
DestBook.save
DestBook.Close
End If
On Error GoTo 0 ' If an error is found in the code, return 0 and end macro
Set DestBook = Nothing
Set SrcBook = Nothing
Have I missed something completely???
