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

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 <hr />

Results (sheet 1 shown):

<img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/zLvKe.png" data-original="https://i.stack.imgur.com/zLvKe.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" />