Paste Chart Image as new worksheet

[gdesreu]

I was hoping someone has an answer for this.

I have a chart that I want to copy and paste as an image on a new worksheet. The excerpt (bottom) works fine except that I want to paste it as a new worksheet NOT on top of a new worksheet. You can do this when moving a chart as new worksheet using

ActiveChart.Location Where:=xlLocationAsNewSheet

but I'm a bit confused as to how to copy the chart as an image and then insert it as a new worksheet.

Any help (as always) is greatly appreciated.

Working Code that pastes as image onto new worksheet.

    Selection.Copy
    Sheets("example").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.ChartArea.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Pictures.Paste.Select

 

[gdesreu]

Okay, I have amended the code so that it pauses at certain times to allow the operation time to complete. Currently, when it pauses, it does so for 1 second, which should be enough time. However, if you still run into problems, you can always try pausing it for more seconds, such as 2 or 3 seconds. Here's the amended code, which includes an additional procedure called Pause.

Option Explicit

Sub test()

    ActiveSheet.ChartObjects("Chart 3").Chart.ChartArea.Copy
   
    Pause 1 'second

    Dim chartSheet As Chart
    Set chartSheet = ThisWorkbook.Charts.Add
   
    Pause 1 'second
   
    With chartSheet
   
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
       
        .Paste
       
        Pause 1 'second
       
        If .HasTitle Then
            .ChartTitle.Caption = .ChartTitle.Caption
        End If
   
        With .SeriesCollection(1)
            .XValues = .XValues
        End With
   
        Dim sr As Series
        For Each sr In .SeriesCollection
            sr.Name = "=""" & sr.Name & """"
            sr.Values = sr.Values
        Next sr
       
    End With
   
End Sub

Sub Pause(ByVal secs As Long)

    Dim endTime As Single
    endTime = Timer + secs
   
    Do
        DoEvents
    Loop Until Timer > endTime
   
End Sub

Does this help?

Domenic, thanks again. I have tried the different timer settings and I am still getting the blank chart if I run it a second time. The interesting thing is, it works fine and does exactly as I want provided I don't change the data on chart3. If I change the data on chart3 it makes a blank chart. If I delete that blank chart and run it again it works. If I dont delete the blank chart the issue still occurs. Its very reproducible (and strange). It seems to blank it right after this:

.SeriesCollection(1).Delete

Do I need to refresh the chart or something if I revise the data in chart 3 before running it again?

 

[gdesreu]

I'm also wondering if it has something to do with a textbox label I am using to add to the y series. I just realized that I have that pointed to a formula like I did with the Chart title. So if I change the data it may incorrectly change the previous charts. Sorry, I thought I had that as a value going in so I didnt mention it before.

 

[Domenic]

The interesting thing is, it works fine and does exactly as I want provided I don't change the data on chart3. If I change the data on chart3 it makes a blank chart.

I tried adjusting the range for the source data, and I also tried changing the range completely, but I have been unable to re-produce the behaviour you describe. I did some further testing by first setting the pause to 1 second, and then creating a loop so that the chart gets created 100 times. All of the charts were created without any problems.

It seems to blank it right after this:

.SeriesCollection(1).Delete

That part of the code deletes any series that might already exist. The reason it's there is that the source data, if one exists, is automatically used when creating the chart. And, when the chart is created using the existing source data, it's created without any formatting or elements that might exist. And so we need to delete the existing series so that we can paste the chart with the existing formatting and elements.

I'm not sure that this will help, but I think it's worth trying. Let's try to activate the newly created chart before deleting, pasting, etc...

    With chartSheet
    
        .Activate
        
        'etc
        '
        '
        
    End With

Does this help?

 

[gdesreu]

I tried adjusting the range for the source data, and I also tried changing the range completely, but I have been unable to re-produce the behaviour you describe. I did some further testing by first setting the pause to 1 second, and then creating a loop so that the chart gets created 100 times. All of the charts were created without any problems.



That part of the code deletes any series that might already exist. The reason it's there is that the source data, if one exists, is automatically used when creating the chart. And, when the chart is created using the existing source data, it's created without any formatting or elements that might exist. And so we need to delete the existing series so that we can paste the chart with the existing formatting and elements.

I'm not sure that this will help, but I think it's worth trying. Let's try to activate the newly created chart before deleting, pasting, etc...

    With chartSheet
   
        .Activate
       
        'etc
        '
        '
       
    End With

Does this help?

Hmm, I added the

.activate

but it still exhibits the same behavior. Since you aren't having this problem I am going to re-create a file from scratch and see if goes away. Maybe I have some sort of corruption in the file of some sort. (I compiled but it still does it) I'm on office 365 but I have a couple older machines I could try it on as well to see if it works there. I'll do some legwork on this in the next couple of days and post the results. I just have that darn project work that they need done first. Again, thanks for the help. It is greatly appreciated.