Referencing a Named Range in a Macro

Tkeller

Board Regular
Joined
Jul 23, 2003
Messages
131
I have developed a formula in EXcel that specifies the plot range for a chart in which the number of rows can vary.

I want to use a MACRO in order to adjust the graph to use this named range. This is what I have

Sub chartfix()
'
' chartfix Macro
'

'
ActiveSheet.ChartObjects("Chart 5").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:="Custrange", PlotBy:=xlRows
ActiveWindow.Visible = False
Windows("ALMV25.xls").Activate
Range("K32").Select
End Sub

I have defined the name custrange as follows:
custrange=OFFSET('Custom Portfolios'!$A$18,0,0,'Custom Portfolios'!$M$1+1,12)

the above macro does not work, as I do not know how to insert custrange as the source data. Please help!
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
How does it not work?

Code:
Sub chartfix() 
' 
' chartfix Macro 
' 

' 
ActiveSheet.ChartObjects("Chart 5").Activate 
ActiveChart.ChartArea.Select 
ActiveChart.SetSourceData Source:=Range("Custrange"), PlotBy:=xlRows 
ActiveWindow.Visible = False 
Windows("ALMV25.xls").Activate 
Range("K32").Select 
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,088
Messages
5,570,147
Members
412,306
Latest member
fabio6
Top