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

 

[Fluff]

Ok, select the Dash sheet & run this

Sub Chk()
Debug.Print "|" & ActiveSheet.Name & "|", ActiveSheet.CodeName
End Sub

Then copy what is in the Immediate window & paste it here. Ctrl G will bring up the immediate window if it's not open

 

[Kamolga]

That is not normal, do you have same problem with

 [COLOR=#242729][FONT=Consolas]Application.Goto thisworkbook.Sheets([/FONT][/COLOR][COLOR=#333333]"Dash"[/COLOR][COLOR=#242729][FONT=Consolas]).Range("E5")[/FONT][/COLOR]

Otherwise maybe try in a new workbook?

 

[Simonc64]

Hi guys sorry for the delay in replying. Ok so this VBA is baffling me but I'm trying to learn and have come up with a code that compiles AND runs - it just doesn't do what im expecting or wanting it to do - can you please have a look and correct it?

My source data is in a tab called "CIC Summary for PM" and my target sheet where I want the chart to locate is called "SMT Scorecard"......this is what I've written

Sub Macro1()
'
' Macro1 Macro
'

'
Dim rng As Range

Set rng = Range("'CiC Summary for PM'!$A$2:$B$7")
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
With Selection
.Left = Sheets("SMT Scorecard").Range("$O$3").Left
.Width = Sheets("SMT Scorecard").Range("$O$3").Width
.Top = Sheets("SMT Scorecard").Range("$O$3").Top
.Height = Sheets("SMT Scorecard").Range("$O$3:$O$7").Height
End With



End Sub

 

[Simonc64]

Remarkably Ive managed to solve most of the issues with the code below - must be in the zone today!!

Just one thing I need to fix......the chart locates dead on the lines of the cells for the top and left of the chart area but is slightly over on the bottom and right.

I have seen a Mr Excel video that quotes this (for example) .Left = Sheets("SMT Scorecard").Range("$O$3").Left, _ but if I add the , _ to the end of my lines I just get error messages



Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("SMT Scorecard").Activate
Range("O3").Select
Dim rng As Range

Set rng = Range("'CiC Summary for PM'!$A$2:$B$7")
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
With Selection
.Left = Sheets("SMT Scorecard").Range("$O$3").Left
.Width = Sheets("SMT Scorecard").Range("$O$3:$O$3").Width
.Top = Sheets("SMT Scorecard").Range("$O$3").Top
.Height = Sheets("SMT Scorecard").Range("$O$3:$O$7").Height

ActiveChart.SetSourceData Source:=rng

End With



End Sub

 

[Kamolga]

Sub Graph()
[COLOR=#008000]'Set Data source[/COLOR]
Dim rng As Range
Set rng = Sheets("CiC Summary for PM").Range("$A$2:$B$7")
[COLOR=#008000]'Make a column graph[/COLOR]
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
[COLOR=#008000]'Link it to the data[/COLOR]
ActiveChart.SetSourceData Source:=rng
[COLOR=#008000]'Put the graph on sheet named "SMT Scorecard"[/COLOR]
ActiveChart.Location Where:=xlLocationAsObject, Name:="SMT Scorecard"
[COLOR=#008000]'Take off the title[/COLOR]
ActiveChart.SetElement (msoElementChartTitleNone)
[COLOR=#008000]'And put the graph in Range "O3:O7"[/COLOR]
With Selection
.Left = Sheets("SMT Scorecard").Range("$O$3").Left
[COLOR=#008000]'if you want Position O3:P7 for graph, replace .Range("$O$3").width by .Range("O3:P3").width[/COLOR]
.Width = Sheets("SMT Scorecard").Range("$O$3").Width
.Top = Sheets("SMT Scorecard").Range("$O$3").Top
.Height = Sheets("SMT Scorecard").Range("$O$3:$O$7").Height
End With
End Sub

 

[Simonc64]

Fantastic, thank you so much for your help guys that works perfectly !! One last question on this topic........

I will have a series of charts that will use the above code, so effectively when I press the buttons, chart 2 will sit on top of chart 1 etc etc but in order to save this is there a way of deleting the chart that's already in place.....so ive pressed button 1 and chart 1 appears, then I need to look another data set so press button 2....can I delete chart 1 on the press of button2 and the creation of chart 2?

Again I have tried but it seems that each chart has a unique name which means this cant be done.....unless you know differently?

Simon

 

[Kamolga]

I will have a series of charts that will use the above code, so effectively when I press the buttons, chart 2 will sit on top of chart 1 etc etc but in order to save this is there a way of deleting the chart that's already in place.....so ive pressed button 1 and chart 1 appears, then I need to look another data set so press button 2....can I delete chart 1 on the press of button2 and the creation of chart 2?

Hi, first time you build the First chart, name it with

Sub Graph()


'Set Data source
 Dim rng As Range
 Set rng = Sheets("CiC Summary for PM").Range("$A$2:$B$7")
'Make a column graph
     ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
     'Link it to the data
     ActiveChart.SetSourceData Source:=rng
     'Put the graph on sheet named "SMT Scorecard"
     ActiveChart.Location Where:=xlLocationAsObject, Name:="SMT Scorecard"
     'Take off the title
     ActiveChart.SetElement (msoElementChartTitleNone)
     'And put the graph in Range "O3:O7"
        With Selection
        .Left = Sheets("SMT Scorecard").Range("$O$3").Left
         'if you want Position O3:P7 for graph,  replace .Range("$O$3").width by .Range("O3:P3").width
        .Width = Sheets("SMT Scorecard").Range("$O$3").Width
        .Top = Sheets("SMT Scorecard").Range("$O$3").Top
        .Height = Sheets("SMT Scorecard").Range("$O$3:$O$7").Height
[COLOR=#ff0000]        .Name = "ChartA"[/COLOR]
        End With
End Sub

Fron now on, you will delete ChartA before building it again using

Sub Graph()
[COLOR=#ff0000]Sheets("SMT Scorecard").ChartObjects("ChartA").Delete[/COLOR]
'Set Data source
 Dim rng As Range
 Set rng = Sheets("CiC Summary for PM").Range("$A$2:$B$7")
'Make a column graph
     ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
     'Link it to the data
     ActiveChart.SetSourceData Source:=rng
     'Put the graph on sheet named "SMT Scorecard"
     ActiveChart.Location Where:=xlLocationAsObject, Name:="SMT Scorecard"
     'Take off the title
     ActiveChart.SetElement (msoElementChartTitleNone)
     'And put the graph in Range "O3:O7"
        With Selection
        .Left = Sheets("SMT Scorecard").Range("$O$3").Left
         'if you want Position O3:P7 for graph,  replace .Range("$O$3").width by .Range("O3:P3").width
        .Width = Sheets("SMT Scorecard").Range("$O$3").Width
        .Top = Sheets("SMT Scorecard").Range("$O$3").Top
        .Height = Sheets("SMT Scorecard").Range("$O$3:$O$7").Height
[COLOR=#ff0000]        .Name = "ChartA"[/COLOR]
        End With
End Sub

Apply same process for Chart B, C,...

 

[Simonc64]

Amazing ! Thank you SO much

Simon

 

[Kamolga]

You are welcome