Encountering error 1004 but unsure how to resolve

Question

Hi I am encountering this issue "Method "SaveAs" of object' _Workbook' failed. But I am not sure what exactly happen.

The purpose of my code is to copy a sheet from the workbook and save it as a single in the xlsb format in sharepoint folder.

Sub SheetSplit()
    '
    'Creates an individual workbook for each worksheet in the active workbook.
    '
    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim sht As Object
    Dim strSavePath As String
    Dim sname As String
    Dim relativepath As String
    Dim TempWB As Workbook
    Set wbSource = ActiveWorkbook
        Sheet6.Copy
        Set wbDest = ActiveWorkbook

        sname = Sheet9.Range("I5") & ".xlsb"
        relativepath = "C:\Users\" & Environ$("Username") & _
    "\SharePoint\Open Project Transition Check - Doc\Project Status Summary\" & sname 'use path of wbSource
        'wbDest.Sheets(1).Range("A1").Clear 'clear filename from new workbook cell A1
         CurrentFormat = ThisWorkbook.FileFormat
        Application.DisplayAlerts = False
        ActiveWorkbook.CheckCompatibility = False
        ActiveWorkbook.SaveAs Filename:=relativepath, FileFormat:=xlExcel12 _
          , CreateBackup:=False
        Application.Wait (Now + TimeValue("0:00:01"))
        Application.DisplayAlerts = True


        wbDest.Close False 'close the newly saved workbook without saving (we already saved)

    'Next

    MsgBox "Status Summary Saved"

End Sub

Answer 1

okay Kai your code has lots error so lets clean em up.

Tested on Excel 2010

Option Explicit
Sub SheetSplit()
    '// Creates an individual workbook for each worksheet in the active workbook.
    Dim wbDest         As Workbook
    Dim wbSource       As Workbook
    Dim sht            As Object
    Dim strSavePath    As String
    Dim sname          As String
    Dim relativepath   As String
    Dim TempWB         As Workbook

    Set wbSource = ActiveWorkbook
        Sheets.copy
        Set wbDest = ActiveWorkbook

        sname = Sheets("Sheet1").Range("I5").Text & ".xlsb"
        relativepath = "C:\Users\" & Environ$("Username") & _
        "\Desktop\Temp\" & sname 'use path of wbSource

        Application.DisplayAlerts = False

        ActiveWorkbook.CheckCompatibility = False

        ActiveWorkbook.saveas FileName:=relativepath, _
                              FileFormat:=50, _
                              CreateBackup:=False

        Application.Wait (Now + TimeValue("0:00:01"))

        Application.DisplayAlerts = True

        '// close the newly saved workbook without saving (we already saved)
        wbDest.Close False

        MsgBox "Status Summary Saved"
End Sub

See SaveAs

To save on server use \\server-name FYI it only works if it's a local server, on your network