VBA code to fix pivot table chart in position on worksheet, excel 2013, windows

[llblome]

Hi, I am using the following VBA code to move/fix a selected chart to a specific location on a worksheet. The code runs fine, but I end up with 2 duplicate charts: 1 in the new position, and the original chart in its original position. I would appreciate any help in how to fix the code to just move a selected chart.

Sub FixChartPosition ()
Dim Ch As ChartObject
Set Ch=Worksheets("Sheet1").ChartObjects(1)
With Ch

.Top=Range("A250".Top​

.Width=Range("A2150:D2170").Width
.Height=Range("A2150:D2170").Height
​

End With
End Sub

I am very new to VBA. Thanks!

​

​

 

[llblome]

Would really appreciate help on this! Thanks.

 

[Andrew Poulsom]

Welcome to MrExcel.

Your code works fine for me in Excel 2013, once I fix the compile error on this line:

.Top = Range("A250").Top

Maybe you have more charts on the worksheet than you think.

 

[UHsoccer]

Trying to use this method to more precisely place my charts, however

I track the values of where I expect the top of the chart to be and where it actually shows up

As I add charts the difference in expected placement and actual placement keeps growing

here is the logic

Sub TrendChart_Top()

    ' the data used to create the chart is tabulated and chart is placed 5 rows below the last text in column A
    '  all rows are of equal height  of 15

    Dim toSht As String
    Dim numShp As Integer, rowShpTop As Long, xx As Long
    
    'toSht = salesTrendSheet '  global variable in the program
    toSht = "SalesTrend_2012"
    
    numShp = ActiveSheet.Shapes.Count  '  last chart number that was created
    rowShpTop = Application.ActiveSheet.UsedRange.Rows.Count + 3 ' last row with text  +  3 (for spacing)

    Set Ch = Worksheets(toSht).ChartObjects(numShp)
    With Ch
        .Top = Range("B" & rowShpTop).Top  ' place in column B
    End With

    xx = 101    '  used to allow program debug and inspect settings (by "clicking" on the left border in the VB window)

End Sub

the results is

Chart row actual
1 23 25
2 56 62
3 89 99
4 122 135
5 155 172

So the difference keeps growing ....

Any help appreciated

 

[Andrew Poulsom]

Why are you applying UsedRange to ActiveSheet rather than Worksheets(toSht)? Also UsedRange may not be returning what you expect.

 

[UHsoccer]

In my case Active sheet happens to be the same as toSht, so I could change the "Worksheet(toSht)" to "ActiveSheet" and visa versa
I did make the change to use "Active" only, but the behavior remains the same

UsedRange is returning the correct values (I have debug on so I can inspect the values that the various variables have (at the xx = 101 stage)

It is the mysterious shift downward of where the plots are placed which is puzzling me

Thanks

 

[Andrew Poulsom]

Can you post some code that actually demonstrates the behaviour you are seeing?

 

[UHsoccer]

Andrew,

Created a "reduced" version of the program which I started in 2005. I was a serious rookie at the time and have progressed to a modest rookie (that self evaluation is based on reviewing your comments and code samples)

As I removed excessive data, the program behaved differently. I deleted the sheets on which I created the charts and then added a new sheet and renamed it to be consistent with what the program expected.

Mysteriously, the charts locked correctly into the cell that I defined as the "Top" of the chart. All four sheets for charts (one for each year 2012 through 2015) exhibited the same behavior !!

No real answer as to what was causing the problem !!

In summary, it is fixed, yet I do realize that there is something fishy.

Thanks for your patience and if I find the answer I will surely post it here.

Henry