Save Table Data/Formatting as PNG using VBA

Question

I am currently trying to save a formatted range of data from Excel using vba. I run a separate macro to populate the range and format the data. Then I am using a button to run a macro that should save the range as a PNG. The macro saves the correct image about half of the time. The other half, the code runs without error, but saves a blank white image instead of the formatted range. There is no way to tell if it will save the image or blank white. When stepping into the code, The problem seems to occur when I copy the range as a picture onto a temporary chart. Again, there is no way to know if it will work or not from run to run.

Currently, I am using these steps.

'define the range to be selected
     Set = rng = Worksheets("Whatver Sheet I'm Using").range("A1:E1)"  

'get the save location
     filepath = Application.GetSaveAsFilename(FileFilter:="PNG Files (*.png), *png", Title:="Save As")

'Add a temporary worksheet
Set tempsheet = Worksheets.Add

'check to see if the user decided to cancel the save
If filepath = "False" Then
     MsgBox ("Operation Cancelled")
     Exit Sub
End If

'create a chart
    Charts.Add
    ActiveChart.Location where:=xlLocationAsObject, Name:=tempsheet.Name


'paste range onto the chart
    rng.CopyPicture appearance:=xlScreen, Format:=xlPicture
    
    Set tempchart = ActiveChart
    tempchart.Paste

'modify the chart
    Set temppic = Selection
    With tempchart.Parent
        .Width = temppic.Width
        .Height = temppic.Height
    End With

'export the chart
    tempchart.Export filepath


'delete temporary objects without questioning the user
    Application.DisplayAlerts = False
    tempsheet.Delete
    Application.DisplayAlerts = True

'cleanup
    Application.CutCopyMode = False

I didn't use dim to define anything, and I honestly don't understand when using dim is appropriate or not. Stepping into the code shows that when pasting the chart using "tempchart.paste" either the formatted range will be pasted or a blank white range will be pasted. I am not sure if the problem is there or somewhere else. I am also open to rethinking my approach if anyone has a suggestion for a different way to do this. Any help is appreciated.

Answer 1

Further to the comments above, try this

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim wsTemp As Worksheet
    Dim rng As Range
    Dim FilePath As Variant
    Dim objChrt As ChartObject
    
    '~~> This is the worksheet which has the range
    Set ws = Worksheets("Whatver Sheet I'm Using")
    '~~> Define the range to be copied
    Set rng = ws.Range("A1:E1")
    
    '~~> Get the save location
    FilePath = Application.GetSaveAsFilename(FileFilter:="PNG Files (*.png), *png", Title:="Save As")
    
    '~~> Check if user pressed cancel
    If FilePath = False Then
        MsgBox ("Operation Cancelled")
        Exit Sub
    End If
    
    '~~> Add a temp worksheet
    Set wsTemp = Worksheets.Add
    
    With wsTemp
        Set objChrt = .ChartObjects.Add(100, 100, rng.Width, rng.Height)
        
        '<~~ In some cases you may have to use .Select (eeesh!!!). Else the image will not paste.
        objChrt.Select
        
        '~~> Do the copy just before paste.
        rng.CopyPicture appearance:=xlScreen, Format:=xlPicture
        
        DoEvents
        objChrt.Chart.Paste
        DoEvents
    End With
    
    'export the chart
    objChrt.Chart.Export FilePath

    'delete temporary objects without questioning the user
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True

    'cleanup
    Application.CutCopyMode = False
End Sub