Saving workbooks in VBA

[storemannequin]

I keep getting an error when I'm trying to save each new workbook to the original/activeworkbooks file path. I don't see why:

Sub WeeklyNotification()

Application.ScreenUpdating = False

Set ActveWb = ActiveWorkbook
Set ActveWs = ActveWb.ActiveSheet

FnlRw = ActveWs.Cells(Rows.Count, 1).End(xlUp).Row
FnlCol = ActveWs.Cells(1, Columns.Count).End(xlToLeft).Column



For i = 2 To FnlRw
    With ActveWs.Cells(i, 1)
        ThisPln = ActveWs.Cells(i, 1).Value
        ThisNme = ActveWs.Cells(i, 2).Value

    Set SlsWb = Workbooks.Add(template:=xlWBATWorksheet)
    Set SlsWs = SlsWb.Worksheets(1)
    ActveWs.Range("A1").CurrentRegion.Rows(1).Copy Destination:=SlsWs.Range("A1")
    ActveWs.Cells(i, 1).Resize(, FnlCol).Copy Destination:=SlsWs.Cells(2, 1)
    SlsWs.Name = "UOM Diff"
    
    
    
   
   End With
   
    FN = ThisNme & " Discontinued Item List " & Date & ".xlsx"
    FP = ActveWb.Path & Application.PathSeparator
    
    SlsWb.SaveAs Filename:=FP & FN
    SlsWb.Close savechanges:=False
  
    
Next i

End Sub

 

[Joe4]

I think the problem is this line:

    FN = ThisNme & " Discontinued Item List " & Date & ".xlsx"

"Date" returns slashes, which are not legal characters for file names. Try something like this instead:

    FN = ThisNme & "Discontinued Item List " & Format(Date,"mm-dd-yyyy") & ".xlsx"

 

[Andrew Poulsom]

You need to add the FileFormat argument to SaveAs:

http://www.rondebruin.nl/saveas.htm

Also you should Format Date to remove any backslashes.