Same chart, multiple worksheets, dynamic data ranges

Post your sample workbook with 2-3 pages in it and the code you're using currently, I'll see if I can adapt it for you to be dynamic.

sample code:

Sub GRAPHS()
'
' GRAPHS Macro
'

'

Dim J As Integer


For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active


With ActiveSheet
sWord = ActiveSheet.Name
    Range("A1:D4").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlCylinderColClustered
    ActiveChart.SetSourceData Source:=Sheets(sWord).Range("$A$1:$D$4")
    ActiveChart.ApplyLayout (1)
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Colors by Year"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Colors by Year"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 14).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 6).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(7, 8).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.PlotArea.Select
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleVertical)
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "%"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "%"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 1).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 1).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 = 10
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    End With
    Next
End Sub

sample workbook, worksheet 1 'GROUP 1'

YEAR RED GREEN YELLOW
2012 30 30 40
2013 40 30 20
2014 30 35 35


WORKSHEET2 'GROUP 2'
YEAR RED GREEN YELLOW ORANGE
2012 30 30 30 10
2013 20 20 30 30
2014 30 35 35 0


WORKSHEET 3 'GROUP 3'

YEAR RED GREEN YELLOW ORANGE PURPLE
2012 0 0 30 20 50
2013 20 20 30 30 0
2014 20 35 10 0 35

Like so:

Sub GRAPHS()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        .Activate
        sWord = .Name
        Range("A1").CurrentRegion.Select
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlCylinderColClustered
    '    ActiveChart.SetSourceData Source:=Sheets(sWord).Range("$A$1:$D$4")
        ActiveChart.SetSourceData Source:=Sheets(sWord).Range(Range("$A$1").CurrentRegion.Address)
        ActiveChart.ApplyLayout (1)
        ActiveChart.ChartTitle.Select
        ActiveChart.ChartTitle.Text = "Colors by Year"
        Selection.Format.TextFrame2.TextRange.Characters.Text = "Colors by Year"
        With Selection.Format.TextFrame2.TextRange.Characters(1, 14).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With
        With Selection.Format.TextFrame2.TextRange.Characters(1, 6).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(7, 8).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.PlotArea.Select
        ActiveChart.SetElement (msoElementPrimaryValueAxisTitleVertical)
        ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "%"
        Selection.Format.TextFrame2.TextRange.Characters.Text = "%"
        With Selection.Format.TextFrame2.TextRange.Characters(1, 1).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With
        With Selection.Format.TextFrame2.TextRange.Characters(1, 1).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 = 10
            .Italic = msoFalse
            .Kerning = 12
            .Name = "+mn-lt"
            .UnderlineStyle = msoNoUnderline
            .Strike = msoNoStrike
        End With
    End With
Next ws

End Sub