CODEWITHSUNDEEP
vbaexcelexcel-2007
OO_Learner
OO_Learner

Reputation: 73

macro that loops through worksheets

The macro below performs a calculation and generates a bar chart. At the moment it works for the first worksheet (Sheet1) I would like to be able to repeat the same macro on all worksheets in my excel workbook. Is there a simple way to do this? Thanks in advance.

Sub MyReport()
     Workbooks.Open Filename:= _
        Application.GetOpenFilename
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=SUM(C[-5])"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    Range("G1:I2").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("'Sheet1'!$G$1:$I$2")
    ActiveChart.ChartType = xlColumnStacked
End Sub

Upvotes: 0

Views: 221

Answers (1)

Automate This
Automate This

Reputation: 31364

Here is how to use a for each loop to apply code to each sheet. I've also simplified some of your code.

Sub MyReport()
    Dim Wkb As Workbook
    Dim Ws As Worksheet
    Dim chrt As Chart

    Set Wbk = Workbooks.Open(Filename:=Application.GetOpenFilename)

    For Each Ws In Wbk.Worksheets
        Ws.Range("G2:I2").FormulaR1C1 = "=SUM(C[-5])"
        Ws.Range("J2").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"

        Set chrt = Ws.Shapes.AddChart.Chart
        chrt.SetSourceData Source:=Ws.Range("$G$1:$I$2")
        chrt.ChartType = xlColumnStacked
    Next Ws
End Sub

Results (sheet 1 shown):

enter image description here

Upvotes: 3

Related Questions