Hello Everyone,
I am new to Macro's and have been googling/youtub'ing a lot so far, but I am now stuck. I have a spreadsheet with 20 sheets in that are all identical and house data within a table (all of which are the same amount of rows). The data from each sheet feeds into a graph that is on the same sheet. I have noticed that there is an error in the graph and it is not selecting all the rows in the table. I wrote a macro using the macro recorded and recorded the actions on sheet 1. The steps I recorded were to change the graph range to include all rows in the table and also a few other chat formatting things.
I applied the macro to the other sheets and I thought it worked fine. However the graphs on sheets 2-20 have now all been updated so that the ranges pull from sheet 1 and not the sheets that the graphs are located on. I dont know if im missing something really simple or a basic understanding but I cant for the life of me figure it out. I just want the macro to run within the sheet and not use the range from sheet 1. Here is the VBA code below.
In this example 'sheet 1' that I have mentioned above is "Blend 2.1 (intra-gran)"
Hoping anyone can help! Thanks
Sub newRETAINED()
'
' newRETAINED Macro
'
'
Columns("H:Z").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.SmallScroll Down:=54
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(1).XValues = _
"='Blend 2.1 (intra-gran)'!$B$22:$B$30"
ActiveChart.FullSeriesCollection(1).Values = _
"='Blend 2.1 (intra-gran)'!$F$22:$F$30"
ActiveChart.FullSeriesCollection(2).Name = "='Blend 2.1 (intra-gran)'!$M$48"
ActiveChart.FullSeriesCollection(3).Name = "='Blend 2.1 (intra-gran)'!$M$49"
ActiveChart.FullSeriesCollection(4).Name = "='Blend 2.1 (intra-gran)'!$M$50"
ActiveWindow.SmallScroll Down:=39
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(3).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowRange = False
ActiveChart.FullSeriesCollection(2).DataLabels.Select
Selection.ShowSeriesName = True
End Sub
I am new to Macro's and have been googling/youtub'ing a lot so far, but I am now stuck. I have a spreadsheet with 20 sheets in that are all identical and house data within a table (all of which are the same amount of rows). The data from each sheet feeds into a graph that is on the same sheet. I have noticed that there is an error in the graph and it is not selecting all the rows in the table. I wrote a macro using the macro recorded and recorded the actions on sheet 1. The steps I recorded were to change the graph range to include all rows in the table and also a few other chat formatting things.
I applied the macro to the other sheets and I thought it worked fine. However the graphs on sheets 2-20 have now all been updated so that the ranges pull from sheet 1 and not the sheets that the graphs are located on. I dont know if im missing something really simple or a basic understanding but I cant for the life of me figure it out. I just want the macro to run within the sheet and not use the range from sheet 1. Here is the VBA code below.
In this example 'sheet 1' that I have mentioned above is "Blend 2.1 (intra-gran)"
Hoping anyone can help! Thanks
Sub newRETAINED()
'
' newRETAINED Macro
'
'
Columns("H:Z").Select
Selection.EntireColumn.Hidden = False
ActiveWindow.SmallScroll Down:=54
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(1).XValues = _
"='Blend 2.1 (intra-gran)'!$B$22:$B$30"
ActiveChart.FullSeriesCollection(1).Values = _
"='Blend 2.1 (intra-gran)'!$F$22:$F$30"
ActiveChart.FullSeriesCollection(2).Name = "='Blend 2.1 (intra-gran)'!$M$48"
ActiveChart.FullSeriesCollection(3).Name = "='Blend 2.1 (intra-gran)'!$M$49"
ActiveChart.FullSeriesCollection(4).Name = "='Blend 2.1 (intra-gran)'!$M$50"
ActiveWindow.SmallScroll Down:=39
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.FullSeriesCollection(3).DataLabels.Select
Selection.ShowSeriesName = True
Selection.ShowRange = False
ActiveChart.FullSeriesCollection(2).DataLabels.Select
Selection.ShowSeriesName = True
End Sub