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 point you in the correct direction. This simply loops through each sheet in the source workbook and check to see if it is in the 'do not copy' list (you can add more using AND/OR statements) and then copies the sheets to the end position of the target workbook.
Excel usually takes care of all referencing

```
Sub copySheets()

    Dim ws As Worksheet
    Dim Source As Workbook
    Dim target As Workbook

    Set target = Workbooks("Test.xlsx")

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "DashBoard" Then
            ws.Copy Before:=target.Sheets(target.Sheets.Count)
        End If
    Next

End Sub

```