Copy Sheet to New Workbook with VBA

Copying data to a new workbook, saving it and moving to the next item in the list and doing the same thing is not too arduous a task - unless you havea great many files to create.  When this is the case the procedure can be time consuming.  Let's have a look at how VBA can help with this task.  
 
With the following Excel example I will take each item in a list, isolate the data related to that item and create a new workbook with only the data related to that item.  For this Excel article I have cut down one of the Dashboards to use as a demonstration file.

I have taken the button out of the file to ensure there are no external links after the procedure is run. So simply click on The Developer Tab - Click Macros and Highlight MoveSheet and Click Run.



Option Explicit
Sub
MoveSheet() 'Moving a sheet from the current workbook to new workbook with Excel VBA.
Dim fname As String
Dim i As Integer

Application.DisplayAlerts=False

For i=1 To 5 'Number of Files to Copy
Range("B" & i)=True
fname="C:\Test\Files\" & Sheet5.[D1] & ".xls"
Sheets("Summary").Copy
[A1:O62].Value=[A1:O62].Value

ActiveWorkbook.SaveAs fname 'Save File
ActiveWorkbook.Close
Next i
Application.DisplayAlerts= True
End Sub

Delinking the Chart from the Source Data


Often these Excel files have charts and using the above method will show the chart being linked to the source file. This is not always ideal particularly if you are emailing the file to others. It would be preferable if the file had no external links. With that in mind the following procedure will delink charts from their source data after the sheet is copied in its entirety.

I first saw this Delink procedure in 2005 on Jon Peltier's website. I have adapted it over the years and reduced it to its current state.

Option Explicit
Sub
MoveSheet() 'Moving a sheet from the current workbook to new wb with Excel VBA.
Dim fname As String
Dim ocht As ChartObject
Dim i As Integer

Application.DisplayAlerts=False

For i=1 To 4 'Number of files to Copy
[b2]=i
fname="C:\Test\Files\" & Sheet2.[B5] & ".xls"
Sheets("Summary").Copy
[A10:O62].Value=[A10:O62].Value

For Each ocht In ActiveSheet.ChartObjects
ocht.Select
Call DelinkChartFromData
Next ocht

ActiveWorkbook.SaveAs fname 'Save File
ActiveWorkbook.Close
Next i
Application.DisplayAlerts=True
End Sub

'This process delinks a chart from its source.

Private Sub DelinkChartFromData() ' Excel VBA to delink a chart
Dim mySeries As Series
Dim sChtName As String

On Error Resume Next
sChtName=ActiveChart.Name
If Err.Number <> 0 Then
MsgBox "This functionality is available only for charts & or chart objects"
Exit Sub
End If
If TypeName(Selection)="ChartObject" Then
ActiveSheet.ChartObjects(Selection.Name).Activate
End If
On Error GoTo 0

For Each mySeries In ActiveChart.SeriesCollection 'Loop through series removing links to primary xl file
' Convert X and Y Values to arrays of values
On Error GoTo errortrapper
mySeries.XValues=mySeries.XValues
mySeries.Values=mySeries.Values
mySeries.Name=Null
Next mySeries
errortrapper:
Exit Sub

End Sub