Moving chart location in VBA

[tabbytomo]

Hiya, so thanks to some of you I'm 99% done with my pet project! I have only one tiny thing left that I can't for the life of me figure out.

My below code selects a range based on a criteria, copies that range, pastes it into a chart, then exports the chart as a .jpg to a specified folder. However, the charts that are created are getting in the way of some of the subsequent images. Could someone assist with me with how I move the chart out to at least column AM to keep it out the way. Current code below;

Sub MakeTeamSnapshot(team As Variant)
    Dim PerformanceSheet As Worksheet
    Set PerformanceSheet = Sheets("Flu Database 1")
    Dim sh As Shape
     
    With PerformanceSheet
        .Range("$A$1:$Y$1500").AutoFilter Field:=15, Criteria1:=team
        Dim tmpChart As Chart
        Set sh = PerformanceSheet.Shapes(PerformanceSheet.Shapes.Count)
        Set tmpChart = Charts.Add
                tmpChart.ChartArea.Clear
        tmpChart.Name = "PicChart" & (Rnd() * 10000)
        Set tmpChart = tmpChart.Location(Where:=xlLocationAsObject, Name:=PerformanceSheet.Name)
        tmpChart.ChartArea.Width = 500
        tmpChart.ChartArea.Height = 800
    'Paste range as image to chart
        .Range("A1").CurrentRegion.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
        tmpChart.ChartArea.Select
        tmpChart.Paste
        'Save chart image to file
        Dim fileSaveName As Variant
        fileSaveName = SAVE_FOLDER & Replace$(team, " ", "") & "." & SAVE_FORMAT
          tmpChart.Export Filename:=fileSaveName, FilterName:=SAVE_FORMAT
        'Clean up
        tmpChart.Parent.Delete
    End With

End Sub

 

[NdNoviceHlp]

Not really following how the charts are getting in the way of subsequent images? The chart code deletes the chart after it is exported to an image file. Do you want to place the top left chart corner at AM row 1? Maybe a bit more info. Dave

 

[tabbytomo]

Sorry after more testing some of the ranges are so large (say A1:D90) that when the chart is created it sits over part of the range. So when the screenshot happens, you see part of the chart covering part of the range. Just aesthetically displeasing.

I have solved this now though by inserting this line after the lines that define the height and width:

tempChart.Parent.Left = 2000

 

[NdNoviceHlp]

Glad U got it figured out. Thanks for posting your outcome. Here's a link to chart sizing and placement. HTH, Dave

Resizing and Moving Excel Charts with VBA

Use VBA to resize and reposition your charts

peltiertech.com