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:
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