-1

Ok. I have been rattling my brain now for hours and scrolling through the internet for even longer and I can't seem to work out how to save a specific sheet in a work book as a new workbook with values only. Quick back story, I have a work book with heaps of pages with one being a report generator. The "Standard Report" sheet consist of graphs and tables that refer to cells in other sheets in the workbook. I want to create a macro that saves the "Standard Report" sheet as it's own workbook with the current values only.

Is this even possible? If someone could give me a hand or throw me some helpful links that would be extremely appreciated.

Jerremy Leedham
  • 97
  • 4
  • 13

1 Answers1

0

I'd recommend copying the sheet to a new workbook, breaking links in that workbook, and then saving. Consider something like the below (untested, intended to point you in the right direction only).

Sub StaticCharts()

Dim sh As Worksheet
Dim wb As Workbook
Dim cht As ChartObject
Dim scoll As Series


'Assumes the active worksheet contains the charts; change below to an absolute reference if possible
Set sh = ActiveSheet

Set wb = Workbooks.Add 'Adds a new workbook

sh.Copy Before:=wb.Sheets(1) 'Copies sheet with charts to new workbook

'Delete empty sheets in destination workbook
On Error Resume Next
wb.Sheets(4).Delete
wb.Sheets(3).Delete
wb.Sheets(2).Delete
On Error GoTo 0

'Break links in all charts
For Each cht In sht.ChartObjects
    For Each scoll In cht.Chart.SeriesCollection
        scoll.Values = scoll.Values
        scoll.XValues = scoll.XValues
        scoll.Name = scoll.Name
    Next scoll
Next cht

End Sub

See this (old) article for more information: https://support.microsoft.com/en-us/kb/213443

Additional resources:

Community
  • 1
  • 1
Nick Peranzi
  • 1,375
  • 1
  • 9
  • 24