Applying same VBA code on all sheets in a workbook

Question

I have a question regarding VBA. I am trying to apply the below code to all the sheets in a workbook. The workbook contains numerous worksheets but have all the datapoints in the same cells The only difference are the sheet's names. So Basically "MoneyMarket" is just a name of one sheet of the workbook. Tried using for each sheet but got kind of stuck of how to apply this

Dim YRange As Integer, ProjectionRange As Integer
Dim XRange As Range
Dim I As Integer
Sub DrawChart()
    Set XRange = Sheets("MoneyMarket"). _
Range("R8:R" &   Sheets("MoneyMarket").Range("R8").End(xlDown).Row)
    ProjectionRange = Sheets("MoneyMarket").Range("T54").End(xlDown).Row
    YRange = Sheets("MoneyMarket").Range("S8").End(xlDown).Row
    Sheets("MoneyMarket").Range("S8:S" & YRange).Select
    Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="MoneyMarket"
ActiveChart.SeriesCollection.Add Source:=Sheets("MoneyMarket").Range("T8:X" & YRange)
ActiveChart.ChartType = xlLine
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(1).XValues = XRange
For I = 2 To 6
    ActiveChart.SeriesCollection(I).Select
    With Selection.Format.Line
    .DashStyle = msoLineDash
    End With
Next I

End Sub

Answer 1

a very quick search in Google will give you the answer on how to loop through all sheets in a workbook. This is just an example

   Dim WS_Count As Integer
   Dim I As Integer

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     WS_Count = ActiveWorkbook.Worksheets.Count

     ' Begin the loop.
     For I = 1 To WS_Count
        ' Insert/Modify your code here. It will be applied to each sheet.

       'For example to get their names
       msgbox ActiveWorkbook.Worksheets(I).Name
     Next I

Answer 2

If you are interested on a loop across some sheets in a workbook, I recommend you use an array like that:

Sub loopAcrossSheets()
    temp = Array("Sheet1", "Sheet3", "SheetX")
    For Each SheetName In temp
        DrawChart (SheetName)
    Next
End Sub

Then you should put the input string on your Sub:

Sub DrawChart(SheetName As String)

And, at last, replace the sheet name ("MoneyMarket") by SheetName! This Works for me!

Answer 3

You have to create a Module, and refer directly to ActiveSheet in your code, check the code below

Dim YRange As Integer, ProjectionRange As Integer
Dim XRange As Range
Dim I As Integer
Public Sub DrawChart()
    Dim ActiveSheetName as String
    ActiveSheetName = ActiveSheet.Name
    Set XRange = ActiveSheet. _
Range("R8:R" &   ActiveSheet.Range("R8").End(xlDown).Row)
    ProjectionRange = ActiveSheet.Range("T54").End(xlDown).Row
    YRange = ActiveSheet.Range("S8").End(xlDown).Row
    Sheets("MoneyMarket").Range("S8:S" & YRange).Select
    Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActiveSheetName
ActiveChart.SeriesCollection.Add Source:=ActiveSheet.Range("T8:X" & YRange)
ActiveChart.ChartType = xlLine
ActiveChart.Axes(xlCategory).Select
ActiveChart.SeriesCollection(1).XValues = XRange
For I = 2 To 6
    ActiveChart.SeriesCollection(I).Select
    With Selection.Format.Line
    .DashStyle = msoLineDash
    End With
Next I

End Sub