Using VBA to save sheet from main file.

[i200yrs]

Hello All...need some advice...I have a main.xlsm file with sheet2. I want to create a button that when i click it will save the sheet2 into separate workbook.
I found the code below is useful, but when sheet2 is open and i click the button, it got error. Please help how to resolve.
I want something message alert pop-out instead of error...thanks in advance.

Sub sb_Copy_Save_Worksheet_As_Workbook()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    ThisWorkbook.Sheets("Sheet2").Copy Before:=wb.Sheets(1)
    wb.SaveAs "C:\Users\Desktop\1_Excel\VBA\SheetCopySave\Sheet2.xlsx"
    wb.Close
End Sub

 

[Herakles]

The first line will create a new workbook and copy the Sheet2 sheet into that workbook as
the only sheet.

The second line will close and save the workbook.

Sub sb_Copy_Save_Worksheet_As_Workbook()
    
    ThisWorkbook.Sheets("Sheet2").Copy
    
    ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\Users\Desktop\1_Excel\VBA\SheetCopySave\Sheet2.xlsx"

End Sub

 

[i200yrs]

I still got an error....the main file was saved somewhere from the server folder...If some user open the sheet2 file, i cant be able to save...it got an error.

The first line will create a new workbook and copy the Sheet2 sheet into that workbook as
the only sheet.

The second line will close and save the workbook.

Sub sb_Copy_Save_Worksheet_As_Workbook()
   
    ThisWorkbook.Sheets("Sheet2").Copy
   
    ActiveWorkbook.Close SaveChanges:=True, Filename:="C:\Users\Desktop\1_Excel\VBA\SheetCopySave\Sheet2.xlsx"

End Sub

 

[Herakles]

Does this line work on it's own?

ThisWorkbook.Sheets("Sheet2").Copy

Is the new workbook open by more than one person at the same time?

 

[i200yrs]

Hello all i resolve this one. Found this code:

Public Function FileInUse(sFileName) As Boolean
    On Error Resume Next
    Open sFileName For Binary Access Read Lock Read As #1
    Close #1
    FileInUse = IIf(Err.Number > 0, True, False)
    On Error GoTo 0
End Function


Sub TestFile()
    Dim wb As Workbook

    myFilePath = "C:\WeeklyMeeting\test.xlsx"
    If FileInUse(myFilePath) Then
        MsgBox "File is open. Cannot overwrite!"
    Else