Method Range of Object Global Failed Error in VBA macro

[SBF12345]

Greetings,

I am running into a Method 'Range' of object'_Global' failed error in my macro below

Dim AA as String:    AA = ActiveSheet.Name
ActiveSheet.Activate
Range("A4:K300").Select
ActiveSheet.Shapes.AddChart2(227,xlLine).Select
ActiveChart.SetSourceData Source:=Range("AA!$A$4:$K$300")
ActiveChart.Location Where:=xlLocationAsNewSheet

I am receiving the error on line:

ActiveChart.SetSourceData Source:=Range("AA!$A$4:$K$300")

I suspect it has something to do with the sheet name and possibly variable type. Any ideas why this might be?

 

[DanteAmor]

Try

ActiveChart.SetSourceData Source:=Range("'" & AA & "'!$A$4:$K$300")

 

[Fluff]

Whilst I no nothing about charts, having never used them. Try

ActiveChart.SetSourceData Source:=Range("'" & AA & "'!$A$4:$K$300")

 

[nardagus]

Hello,

You can also write it in this way:

ActiveChart.SetSourceData Source:=Sheets("AA").Range("A4:K300")

 

[Fluff]

As AA is a variable, you would need to remove the quotes from it

 

[DanteAmor]

Test without sheet

ActiveChart.SetSourceData Source:=Range("$A$4:$K$300")

 

[nardagus]

As AA is a variable, you would need to remove the quotes from it

Yes you right. I somehow failed to notice it... :/

 

[SBF12345]

Thanks, got it running!

 

[Fluff]

Glad we could help & thanks for the feedback