mardi 24 février 2015

What does this SaveAs syntax have or does not have that prevents execution?

Have developed code with exact examples on this website, yet will not run and stops at SaveAs... line. Can anyone spot my mistake?


I get this error:


enter image description here


Yet the path C:\dads\Downloads\ does exist, the file is not in use by another program, and it has a different name. What am I missing?



Option Explicit

Sub PickupRoutes_Click()

Dim Rng As Range
Dim Lrow As Integer, NewFile As String

Lrow = 7
Set Rng = ActiveSheet.Range(Cells(2, 33), Cells(Lrow, 38))

'~~> Copy the range from the current Workbook
Rng.Select
Rng.Copy

'Establish a new Workbook and paste range
Application.Workbooks.Add
ActiveWorkbook.ActiveSheet.Range("A1").Select

'~~> Paste rng in Cell A1.
ActiveWorkbook.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'~~>. Save the file
NewFile = "C:\dads\Downloads\" & "PlanMyRoute_" & _
Format(Date, "mm-dd-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs NewFile, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
MsgBox "PlanMyRoute file has been saved "

End Sub

Aucun commentaire:

Enregistrer un commentaire