Question: I have a workbook with multiple sheets, one of which is a dashboard, and one that has a report containing charts based on data contained on the other sheets.

I also have Module 1 which contains all the workbook's VBA.

Is it possible to have a button on the Dashboard, that copies all the sheets to a new Workbook, minus the dashboard sheet and Module 1, and ensures the chart's source points to the newly created workbook. 

The new workbook doesn't need to be saved. "Book1" is fine.

Answer: 
With inspiration from the 2 answers provided, what I ended up doing is moving my VBA to a sheet called Macros, then used this code on a button on the dashboard

```
Private Sub cmdExport_Click()

Dim filename    As String
Dim wb          As Workbook

filename = "C:\Users\xxx\Downloads\Book1.xls"

ThisWorkbook.SaveCopyAs filename

Set wb = Workbooks.Open(filename:=filename)

Application.DisplayAlerts = False

With wb
    .Sheets("Macros").Delete
    .Sheets("Dashboard").Delete
End With

Application.DisplayAlerts = True

End Sub

```
This should work for what you want. Just change the sheet names. This is a simple way of doing it but it requires you to list out each sheet name. There is a way to do all sheets too minus the one you want. I will try and get that to work and post it here in a bit.

```
Sub Copy_Sheets_to_new_workbook()        
' This code will copy the sheets you want to a new workbook    
    Sheets(Array("Your Sheet Name 1", "Your Sheet Name 2", "Your Sheet Name 3")).Select    
    Sheets("Your Sheet Name 1").Activate    
    Sheets(Array("Your Sheet Name 1", "Your Sheet Name 2", "Your Sheet Name 3")).Copy    
End Sub

```