macro that loops through worksheets

Question

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

`Is there a simple way to do this?` - you could use [For Each loop](http://stackoverflow.com/questions/12060855/iterating-through-excel-sheets) — Dmitry Pavliv, Mar 05 '14 at 15:01

Automate This · Accepted Answer · 2014-03-05T15:33:16.513

3

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

edited Mar 05 '14 at 15:33

answered Mar 05 '14 at 15:26

Automate This

30,726
11
60
82

Nice answer but I would `Dim` the `Ws` variable just for best practice. – simpLE MAn Mar 05 '14 at 15:30
Wait! :) Instead of `ActiveWorkbook` maybe it would be good to `Dim WB as WorkBook` and then `Set WB = Workbooks.Open(Filename:=Application.GetOpenFilename)` to include OP's initial code. **Edit:** you just got my +1 :) – simpLE MAn Mar 05 '14 at 15:34
@simpLEMAn I intentionally left that part out but your right, it would be better to stick with the original OP intentions. Added to code. – Automate This Mar 05 '14 at 15:38
Alright, it's also because I'm not a big fan of `ActiveWhatever` because you never know :) – simpLE MAn Mar 05 '14 at 15:41
1

Error handling is a part that change your code from "good" to "excelent":) What would be if user press "Cancel" or select "*.txt" file? See my [answer here for details](http://stackoverflow.com/questions/21723223/open-a-workbook-using-filedialog-and-manipulate-it/21723463#21723463):) – Dmitry Pavliv Mar 05 '14 at 15:46
@OO_Learner; It is Portland Runner's solution :) – simpLE MAn Mar 05 '14 at 17:11

macro that loops through worksheets

1 Answers1

Linked