ActiveSheet

[Deeks]

I am trying to automate some graphing in an excel workbook. The workbook has around 50 sheet all with various names i.e. K11, K12 ect.

I can make my macro work on individual sheets through a button that I have inserted. I would really like to copy the button (and attached macro) to work on any sheet. I am close but the last bit of code I am struggling with is:

"ActiveChart.SetSourceData Source:=Range("'K11'!$B$6:$B$76,'K11'!$AC$6:$AC$76" _
)"

This works on sheet 'K11'.


"ActiveChart.SetSourceData Source:=Range("ActiveSheet$B$6:$B$76,ActiveSheet$AC$6:$AC$76" _
)"

this is my attempt but doesn't work.....

I have used the ActiveSheet in other places in the code to replace K11 and it works great. Is there a way use 'ActiveSheet' to grab the range to graph?

Hope this makes..........

Thanks, you guys are great...

[haripopuri]

Please check if below code works.

Please Login or Register  to view this content.

[Deeks]

Hi all

Thanks for your response Haripopuri, I really appreciate it.

I am going to copy out the whole macro - which is a bit embrising because I just record it and I know it is messy. Your source fix worked but now the macro is comming up with an error further down - particularly at:
"ActiveChart.SeriesCollection(1).Name = "=""Start Year""""

This is a bit wierd as the original code with the sheet K11 named works?

As I said I really appreciate your help.

Regards,
Deeks





"Sub PM_Start_Year()
'
' PM_Start_Year Macro
'

'
Rows("6:76").Select
ActiveWindow.SmallScroll Down:=-57
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range("AC6:AC76"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("B6:DP76")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B6:B76").Select
ActiveWindow.SmallScroll Down:=-48
Range("B6:B76,AC6:AC76").Select
Range("AC6").Activate
ActiveWindow.SmallScroll Down:=-81
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlBarClustered
ActiveChart.SetSourceData Source:=ActiveSheet.Range("B6:B76", "C6:C76")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
ActiveChart.Legend.Select
ActiveChart.SeriesCollection(1).Name = "=""Start Year"""
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Start Year PM Reading Levels"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Start Year PM Reading Levels"
With Selection.Format.TextFrame2.TextRange.Characters(1, 28).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 21).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
With Selection.Format.TextFrame2.TextRange.Characters(22, 7).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select

ActiveChart.ChartTitle.Select
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
ActiveChart.ChartArea.Select
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MaximumScale = 30
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.ClearToMatchStyle
ActiveChart.ChartArea.Select
End Sub"