-5

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.

bd528
  • 886
  • 2
  • 11
  • 29

3 Answers3

1

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
bd528
  • 886
  • 2
  • 11
  • 29
  • "...moving my VBA to a sheet called Macros" What? That doesn't make sense. Saving as a .xlsx file will remove the VBA code, then you just need to delete the unwanted worksheet. – Jon Peltier May 19 '18 at 14:29
  • 1
    However, copying the entire workbook is the easiest way to ensure that the charts link to the right worksheets. So I upvoted this answer and not the others. – Jon Peltier May 19 '18 at 14:31
0

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
AndyW
  • 410
  • 5
  • 17
Mike F.
  • 11
  • 3
  • When using this method, the sheet with charts refers back to the original workbook. – bd528 May 17 '18 at 10:44
  • Sorry i missed that you wanted the data to point to the newly created workbook. – Mike F. May 17 '18 at 10:49
  • What if you moved all the sheets and then deleted the "charts" sheet afterward? I think that would cause the connections to come over with it but break once you delete the worksheet. Something like this: – Mike F. May 17 '18 at 11:06
  • Sheets(Array("Your Sheet Name 1", "Your Sheet Name 2", "Your Sheet Name 3", "Your Sheet Name 4")).Select Sheets("Your Sheet Name 1").Activate SheetsArray("Your Sheet Name 1", "Your Sheet Name 2", "Your Sheet Name 3", "Your Sheet Name 4").Copy Sheets("Your Sheet Name 1").Select ActiveWindow.SelectedSheets.Delete ActiveWorkbook.SaveAs Filename:="C:\Filepath\Book1.xlsx", _ FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False – Mike F. May 17 '18 at 11:10
  • ActiveWorkbook.BreakLink Name:= _ "C:\old_Excel_doc_filepath.xlsm" _ , Type:=xlExcelLinks – Mike F. May 17 '18 at 11:33
  • This will break the link to the old worksheet but you would have to reference the new sheet. This will also kill all the formulas too though. – Mike F. May 17 '18 at 11:34
  • Use this code instead to update the link; ActiveWorkbook.UpdateLink Name:="C:\Filepath\Book1.xlsx" FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False – Mike F. May 17 '18 at 11:36
  • @MikeF. Have a look at [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Using `.Select` and `.Activate` is a bad practice and not needed for that copy action. – Pᴇʜ May 17 '18 at 11:45
  • @PEH Thanks again! I bookmarked the page to reference it in the future. That question is super helpful. – Mike F. May 17 '18 at 11:50
  • So much simpler to make a copy of the original workbook and remove the unwanted worksheet(s). – Jon Peltier May 19 '18 at 14:33
0

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
AndyW
  • 410
  • 5
  • 17