Copying the same sheet multiple times to a new workbook

clatron

New Member
Joined
Nov 14, 2014
Messages
3
I've built a dashboard on an excel sheet, cell Z11 is a drop down validated list which contains the 5 different splits of my data, and changes the appearance of the dash when changed.

My overall aim is to convert the 5 different views into a single PDF with 5 pages. I don't / can't have adobe installed due to work restrictions, so I have disregarded the methods I've found so far that required this.

I thought to run a macro that creates a new workbook containing the 5 sheets, so I can then multi-select these and create a single pdf.

Note that all workbooks are saved in MS Teams locations.

I created an empty xlsm file called "PDF_Creation.xlsm" - it's location is referenced in cell J11

I'm no expert in VBA but I've pulled together the below code to try and start doing this, but for some reason at the stage "Copy dash to new book", my excel closes itself and all books.

I've also added something in to break the link each time a sheet is copied.

Any help or advice would be much appreciated.

VBA Code:
Sub multipdf()
'
' multipdf Macro
'
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim FilePDF As String

Set WB1 = ActiveWorkbook
FilePDF = Range("j11").Value

'Open temp PDF xlsm document
Workbooks.OpenText FILENAME:=FilePDF, Local:=True
Application.DisplayAlerts = False

Set WB2 = ActiveWorkbook

'Set Dash to Split 1
WB1.Activate
Sheets("Dashboard").Select
Range("z11").Value = "Split1"

'Copy dash to new book
Sheets("Dashboard").Copy After:=Workbooks("PDF_Creation.xlsm").Sheets(Workbooks("PDF_Creation.xlsm").Sheets.Count)

'Rename and break links
Sheets("Dashboard").Name = "Split1"
    ActiveWorkbook.BreakLink Name:= _
        "NAME_OF_MY_MAIN_FILE.xlsm" _
        , Type:=xlExcelLinks

'Change Dashboard to second split
WB1.Activate
Sheets("Dashboard").Select
Range("z11").Value = "Split2"

'Copy dashto new book
Sheets("Dashboard").Copy After:=Workbooks("PDF_Creation.xlsx").Sheets(Workbooks("PDF_Creation.xlsx").Sheets.Count)

'Rename and break links
WB2.Activate
Sheets("Dashboard").Name = "Split2"
    ActiveWorkbook.BreakLink Name:= _
        "NAME_OF_MY_MAIN_FILE.xlsm" _
        , Type:=xlExcelLinks
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,224,551
Messages
6,179,470
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top