VBA SaveAs issue

[storemannequin]

I'm trying to save this new workbook to the original workbooks filepath location but when I run the code I get an alert saying that there is already a file of the same name/path saved in my folder and asks would you like to replace - which when I click yes, nothing happens. I'm guessing something is wrong with the code here because I checked before running the macro if there was the same filename I deleted. I hightlighted in red the part not working.

Sub FDD()
Dim WBO As Workbook, WBD As Workbook
Dim WSO As Worksheet, WSD As Worksheet
Dim i As Long, FR As Long, FC As Long
 
FR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
FC = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
 
Set WBO = ActiveWorkbook
Set WSO = ActiveSheet
 
Set WBD = Workbooks.Add(template:=xlWBATWorksheet)
Set WSD = WBD.Worksheets(1)
 
Application.ScreenUpdating = False
 
WSO.Range("A1").CurrentRegion.Rows(1).Copy Destination:=WSD.Range("A1")
WSD.Name = "Danaher Master"
 
nextrow = 2
 
For i = FR To 2 Step -1
 
    If StrComp(CStr(WSO.Cells(i, 2)), "DEXIS ") = 0 Then
        WSO.Cells(i, 1).Resize(, FC).Copy Destination:=WSD.Cells(nextrow, 1)
        WSO.Rows(i).EntireRow.Delete
        nextrow = nextrow + 1
    ElseIf StrComp(CStr(WSO.Cells(i, 2)), "GENDEX") = 0 Then
        WSO.Cells(i, 1).Resize(, FC).Copy Destination:=WSD.Cells(nextrow, 1)
        WSO.Rows(i).EntireRow.Delete
        nextrow = nextrow + 1
    ElseIf StrComp(CStr(WSO.Cells(i, 2)), "KAVO  ") = 0 Then
        WSO.Cells(i, 1).Resize(, FC).Copy Destination:=WSD.Cells(nextrow, 1)
        WSO.Rows(i).EntireRow.Delete
        nextrow = nextrow + 1
    ElseIf StrComp(CStr(WSO.Cells(i, 2)), "P&C   ") = 0 Then
        WSO.Cells(i, 1).Resize(, FC).Copy Destination:=WSD.Cells(nextrow, 1)
        WSO.Rows(i).EntireRow.Delete
        nextrow = nextrow + 1
    ElseIf StrComp(CStr(WSO.Cells(i, 2)), "IMGSCI") = 0 Then
        WSO.Cells(i, 1).Resize(, FC).Copy Destination:=WSD.Cells(nextrow, 1)
        WSO.Rows(i).EntireRow.Delete
        nextrow = nextrow + 1
    End If
 
   FN = "this" & ".xlsx"
 FP = WBO.Path & Application.PathSeparator
 
 WBD.SaveAs Filename:=FP & FN
 
 
 
 
 
Next i
End Sub

 

[Andrew Poulsom]

To use SaveAs in Excel 2007 you should include the FileFormat argument:

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

But maybe that's not the cause of your problem.

 

[AlexPi]

I had a similar problem with a spreadsheet that I have update automatically every day...

What I did was save as the file name with a timestamp after the file name then I go through from time to time and delete any old files.

i used something like this...

Sub filesave()
ActiveWorkbook.SaveAs Filename:="[URL="file://\\munchkin\products$\Monthly"]File Name here[/URL] " & _
  Format(Now(), "mm_dd_yyyy hh mm AMPM"), FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
  WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Not sure if that will help but just thought I'd add my $.02

 

[storemannequin]

Not sure how to incorporate this into the code, the fileformat, that is.

 

[Andrew Poulsom]

Like this?

WBD.SaveAs Filename:=FP & FN, FileFormat:=51

 

[storemannequin]

I'm getting the same alert here.

 

[Andrew Poulsom]

Then the file must exist. Do you have any Workbook_BeforeSave code in your workbook?

 

[storemannequin]

Not sure how to go about checking for that either? sorry...