VBA/Macro to Create Chart and Specify its Position On a New Tab

[Simonc64]

Hi All

Apologies VBA isn't my thing but thinking there must be a way to do this.

My workbook has 2 worksheets, sheet1 and sheet2

My data is stored in Sheet2, cells A38:B43

I need to create a column chart in Sheet1 say in position H5:L9

The idea being that I then attach the macro to a button in Sheet1 to produce the chart

All help appreciated!

Simon

 

[Kamolga]

You can record when you build the graph and then assign this macro to your button.

 

[Simonc64]

No that doesn't work which is why I asked the question - its needs to be positioned precisely in the correct place

 

[Kamolga]

After recorded you can add

[COLOR=#333333]ActiveSheet.Shapes("[/COLOR][COLOR=#ff0000]Your Chart[/COLOR][COLOR=#333333]").Left = Sheet1.Cells(5, 8).Left[/COLOR]
[COLOR=#333333]ActiveSheet.Shapes("[/COLOR][COLOR=#ff0000]Your Chart[/COLOR][COLOR=#333333]").Top = Sheet1.Cells(5, 8).Top[/COLOR]
[COLOR=#333333]ActiveSheet.Shapes("[/COLOR][COLOR=#ff0000]Your Chart[/COLOR][COLOR=#333333]").Height = Sheet1.Range("H5:H9").Height[/COLOR]
[COLOR=#333333]ActiveSheet.Shapes("[/COLOR][COLOR=#ff0000]Your Chart[/COLOR][COLOR=#333333]").Width = Sheet1.Range("H9:L9").Width
[/COLOR]

 

[Simonc64]

OK so ive managed this and it comes u with a run time error - object required - dash is my active sheet

Sub MacroChart2()
'
' Macro1 Macro
'

'
ActiveSheet.Shapes.AddChart (xlColumnClustered)
ActiveSheet.Shapes("Chart2").Left = Dash.Range("E5").Left
ActiveSheet.Shapes("Chart2").Top = Dash.Range("E5").Top
ActiveSheet.Shapes("Chart2").Width = Dash.Range("I5").Width
ActiveSheet.Shapes("Chart2").Height = Dash.Range("E5:E13").Height
ActiveChart.SetSourceData Source:=Range("Dash!$A$8:$B$13")
End Sub

 

[Kamolga]

[COLOR=#333333]ActiveSheet.Shapes("Chart2").Left = [/COLOR][COLOR=#ff0000]sheets("Dash")[/COLOR][COLOR=#333333].Range("E5").Left[/COLOR]
[COLOR=#333333]ActiveSheet.Shapes("Chart2").Top =[/COLOR][COLOR=#ff0000]sheets("Dash")[/COLOR][COLOR=#333333].Range("E5").Top[/COLOR]
[COLOR=#333333]ActiveSheet.Shapes("Chart2").Width = [/COLOR][COLOR=#ff0000]sheets("Dash")[/COLOR][COLOR=#333333].Range("[/COLOR][COLOR=#ff0000]E5:I5[/COLOR][COLOR=#333333]").Width[/COLOR]
[COLOR=#333333]ActiveSheet.Shapes("Chart2").Height = [/COLOR][COLOR=#ff0000]sheets("Dash")[/COLOR][COLOR=#333333].Range("E5:E13").Height[/COLOR]
[COLOR=#333333]ActiveChart.SetSourceData Source:=Range("Dash!$A$8:$B$13")[/COLOR]

If you still have an error, it means your graph is not called Chart2, so might be easier not to name it. Maybe this below could help

Sub Graph()
'Get a column chart in Range E5:E13 From data in A8:B13 -> graph and data on sheet called Dash
Dim rng as Range

Sheets("Dash").Activate
Range("E5").Select

'Your data range for the chart
  Set rng = ActiveSheet.Range("A8:B13")


'Create a chart
  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select

'Position it
   With Selection
    .Left = ActiveCell.Left
    .Width = Sheets("Dash").Range("E5:I5").Width
    .Top = ActiveCell.Top
    .Height = Sheets("Dash").Range("E5:E13").Height
   End With

'Give chart some data
    ActiveChart.SetSourceData Source:=rng

'Determine the chart type
  ActiveChart.ChartType = xlColumnClustered

End Sub

 

[Simonc64]

Hi I appreciate you trying to help me, I have copied the code but still get an error "subscript out of range" and the Sheets("Dash").Activate line is highlighted as wrong?

Simon

 

[Fluff]

That means that you don't have a sheet called "Dash"

 

[Kamolga]

The name of the sheet must be Dash (the one you see when not in VBA -> In VBA you would see Sheet1(Dash) in left column). If you changed the name in properties VBA (which is a very good habit by the way), so that you see something like Dash(watever name the user see) in the column, then Sheets("Dash").Activate should be replaced by

Dash.activate

 

[Simonc64]

That's the problem, I DO have a sheet called dash as that is where all my data is held