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.