Using VBA to save sheet from main file.

i200yrs

New Member
Joined
Dec 18, 2019
Messages
43
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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?
 
Upvote 0
Hello all i resolve this one. Found this code:
VBA 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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top