Hi Nick, and welcome to the forum.
The first thing I'd urge you to do is keep all your data on the same sheet and add an extra column for the Month if it's not already present in your data. i.e. Don't have separate sheets for each month.
With all data on the same sheet it makes any subsequent analysis much much easier. So once you have this the next step would be to apply a dynamic name to this data so that as new data is added you don't need to worry about adjusting the range. Typically I use the name 'Data' and set the range name to be
Formula:
=OFFSET(Sheet1!$A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
Now create on a second sheet a single row of the labels from the data that you want to see extracted each month. Name this range "DataOut"
Also on this sheet add one or maybe two criteria cells say A1:B2 which contain the label(s) for the month column. Whether you use just A1:A2 or A1:B2 as the criteria will depend on what your month column contains. Name this A1:B2 range "crit".
Now you can use an Advanced Data Filter to Extract records from your data sheet to your DataOut range. Either do this manually or probably with the following simple macro
Sub FilterMonth
Sheet1.Range("Data").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range("DataOut"), CriteriaRange:=Sheet2.Range("crit")
End Sub
Finally create a dynamic range name for your filtered data in a similar way to the dynamic name for the data. e.g. i.e. "MonthData"
Formula:
=OFFSET(Sheet2!$A$4,0,0,COUNTA($A:$A),COUNTA($4:$4))
Finally use the range name MonthData as the source range for your chart.
Bookmarks