How to share specific sheets from a workbook using VBA?

To share specific sheets from an Excel workbook using VBA, you will typically want to create a new workbook containing only those specific sheets, and then save or share that new workbook. Below is a step-by-step guide on how you can achieve this using VBA:

Sub ExportSpecificSheets()
    Dim wbSource As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim sheetNames As Variant
    Dim i As Integer
    
    ' Define the source workbook
    Set wbSource = ThisWorkbook ' or Workbooks("YourWorkbookName.xlsx")
    
    ' Define the sheet names you want to export
    sheetNames = Array("Sheet1", "Sheet2", "Sheet3") ' change these to your specific sheets
    
    ' Create a new workbook
    Set wbNew = Workbooks.Add
    ' Delete default sheets in the new workbook
    Application.DisplayAlerts = False
    For Each ws In wbNew.Worksheets
        ws.Delete
    Next ws
    Application.DisplayAlerts = True
    
    ' Copy each specified sheet to the new workbook
    For i = LBound(sheetNames) To UBound(sheetNames)
        wbSource.Sheets(sheetNames(i)).Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
    Next i
    
    ' Optionally save the new workbook
    ' Change "NewWorkbookName.xlsx" to your desired file name and path
    wbNew.SaveAs "C:PathToLocationNewWorkbookName.xlsx"
    
    ' Optionally keep the workbook open or close it
    ' Uncomment the following line to close the workbook after saving
    ' wbNew.Close
    
    ' Notify the user
    MsgBox "Sheets have been exported!"
End Sub

Open VBA Editor: Press `ALT + F11` to open the VBA editor in Excel.
Insert a Module:

In the VBA editor, right-click on any of the items in the “Project Explorer”.
Select `Insert` -> `Module`. This will create a new module where you can write your VBA code.

Write the VBA Code:
Run the Macro:

Close the VBA editor and return to Excel.
Press `ALT + F8` to open the “Macro” dialog box.
Select `ExportSpecificSheets` and click `Run`.

Tips:

Sheet Names: Ensure that you use the exact names of the sheets you want to export. The names are case-sensitive.
File Path: Update the `wbNew.SaveAs` line with the path where you want to save the new workbook.
Error Handling: Consider implementing error handling to manage cases where a sheet does not exist or other errors arise.

This code will create a new workbook containing only the specified sheets, which can then be saved, emailed, or otherwise shared as needed.

facebooklinkedin