Move sheet to new workbook, save, close

[davedave24]

Hello
I'm trying to move a worksheet to a new workbook (thus removing it from the original workbook), save the new workbook in a folder, then close it, and come back to the master workbook.

I found this code on a website a few weeks ago (since lost).

It works okay sometimes, but as the worksheet has a macro button or 2 on it, it produces the "this workbook cannot be saved in macro free format etc." message. I've tried to change it to save as .xlsm but get the same message.

As this is part of a larger procedure I'd like to to just happen without the user seeing any message prompts (unless there's a file duplicate error or something of that ilk).

Dim FName           As String
    Dim FPath           As String
    Dim NewBook         As Workbook

    FPath = "C:\Users\Dave\Desktop\TEST"
    Dim JobNo As String
    JobNo = "J" & Trim(Mid(textQuoteNo, 2, 5))
    FName = JobNo & ".xlsx"

    Set NewBook = Workbooks.Add

    ThisWorkbook.Sheets("Q1888").Move Before:=NewBook.Sheets(1)

    If Dir(FPath & "\" & FName) <> "" Then
        MsgBox "File " & FPath & "\" & FName & " already exists"
    Else
        NewBook.SaveAs Filename:=FPath & "\" & FName
    End If
   [code]

 

[zelgar]

Try changing the following code (add the bold text):

NewBook.SaveAs Filename:=FPath & "\" & FName, [b]FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False[/b]