PDA

View Full Version : Moving Charts in Excel Worksheet



rkc2009
01-21-2009, 07:48 AM
Hello everyone,

I am having a slight difficulty in Excel Macros. I have a macro that creates a chart from a column of data and pastes it into a new sheet. The next step is that I want to shift the location of the chart in the sheet (i.e. I guess Excel calls it increments to the left-right-top-bottom), and it produces code such as this when I do the movements:

ActiveSheet.Shapes("Chart 49").IncrementLeft -166.5
ActiveSheet.Shapes("Chart 49").IncrementTop -51.75

The problem is I don't want the incrementation to pertain simply to Chart 49. I want it to be so that whichever active chart I have, it will increment it to the location I want it. Is there a specific way I can make this happen or am I stuck with this coding? Any suggestions would be great. If anyone needs more clarity, just post it.

Kenneth Hobs
01-21-2009, 01:46 PM
Have you tried?
ActiveChart.IncrementLeft -166.5

rkc2009
01-21-2009, 01:54 PM
This does not work. Is there any other suggestions on how I can solve my problem?

NukedWhale
01-21-2009, 02:09 PM
Does this help?

Sub MoveChart()
'
' Macro that loops through all charts in an active worksheet and moves them to location (?).
'

'
Dim iChart As Long
Dim nCharts As Long

Let nCharts = ActiveSheet.ChartObjects.Count

For iChart = 1 To nCharts
With ActiveSheet.ChartObjects(iChart)
.IncrementLeft -166.5
.IncrementTop -51.75
End With
Next
End Sub

Kenneth Hobs
01-21-2009, 03:14 PM
See the 2nd Sub if you want to move to a certain cell.
Sub MoveActiveChart()
Dim s As String
s = Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(ActiveSheet.Name) - 1)
With ActiveSheet.Shapes(s)
.IncrementLeft -166.5
.IncrementTop -51.75
End With
End Sub

Sub MoveActiveChartToD5()
Dim s As String
s = Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(ActiveSheet.Name) - 1)
With ActiveSheet.Shapes(s)
.Top = Range("D5").Top
.Left = Range("D5").Left
End With
End Sub