Excel Chart - Deleting Source Worksheets 1

[PWD]

Good morning, I have worksheets with charts that reference data from other worksheets and I want to, effectively, copy and Paste As Values so I can then delete the source worksheets. I've had a look and can't figure out how to do it.

After recording a macro I tried this but it didn't do what it did when I was recording it and just appears to duplicate what's already there.

    For Each Chart In ActiveSheet.ChartObjects
    Chart.Activate
    ActiveChart.PlotArea.Select
    ActiveChart.Paste
    Next Chart

Any ideas please?

Many thanks,
D€$

 

[PWD]

This seems to do it:-

    For Each Chart In ActiveSheet.ChartObjects
        Chart.Activate
        ActiveChart.CopyPicture
        ActiveChart.Paste
    Next Chart

Many thanks,
D€$

 

[zelgar]

What your macro does is replace the chart with a picture of the chart. Is that what you want? It sounded like you wanted to copy the source data from the source worksheets and delete the source worksheets.

 

[PWD]

Hi zelgar, what we need to do is just present the finished item (the picture, I guess) for the target audience and not give them access to the source data. However, your post raised an interesting question, "What happened to the original chart?" Answer, "It's still there behind the picture!" It turns out that the data behind the charts is to be kept in the workbook, but what if I wanted to present just the chart worksheets in a separate workbook?

I guess that question is actually still unanswered!

So suggestions please.

Many thanks,
D€$

 

[SkipVought]

Hi,

When you COPY something, if you use the COPY in the Ribbon, there is an option to Copy As... and a choice of pictures. Once you get it down, you could record a macro shortcut.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

The following macro will create a new sheet and copy a picture of all of the charts to the new sheet.

Sub chartpic()
nowsheet = ActiveWorkbook.Name
Workbooks.Add
NewSheet = ActiveWorkbook.Name
Windows(nowsheet).Activate
place = 1
For Each Chart In ActiveSheet.ChartObjects
        Chart.Activate
        ActiveChart.CopyPicture
        X = ActiveChart.Parent.Height
        Windows(NewSheet).Activate
         Cells(place, 1).Select
         ActiveSheet.Paste
         place = place + Int(X / 13)
        Windows(nowsheet).Activate

    Next Chart
End Sub

 

[mintjulep]

Print the chart worksheets as PDF. Give PDF to target audience.

Copy/Paste chart as picture into .... Word document, PowerPoint, .JPG, .PNG whatever and give that to target audience.

 

[PWD]

Thanks zelgar; and then I could delete the original worksheet and rename the new one. Once saved on the network then it would be available to whomsoever required it!

@mintjulip
I would probably SaveAs a pdf and still save that somewhere. Food for thought though.

Many thanks,
D€$

 

[SkipVought]

Unless this is a ONE TIME deal, and in my world, those are few, why would you delete source data/charts that could be used for follow-on work?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, initially this stemmed from some error when I was testing parts of macros that made it appear as if these charts had lost their links to their source worksheets. This is a slimmed-down copy of the original, master, workbook with fewer worksheets so we still have the relevant connections ready for next month's data.

Many thanks,
D€$

 

[combo]

If you still need a chart:
- manually:

https://support.microsoft.com/en-us/kb/214139

- via macro:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

combo

 

[PWD]

Wow!

Many thanks,
D€$