25996

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

Answer1:

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

Recommend

  • VBA - Rename worksheet after source file
  • Is there anyway to make SOMETHING automatically add selected file extension to filename, when OPENFI
  • Activesheet.Shapes.AddPicture
  • Is there a difference between QFileDialog strings in PyQt4 and PyQt5?
  • I got the problem in Crystal Report- Error Messsage:Load report failed
  • MATCH() function in VBA
  • IronPython - Run an Excel Macro
  • Catch change event on input field dynamically added to Jquery Datatables table
  • Create an object whose type is in a string parameter
  • Excel Range in C# using interop.Excel. Range both empty and not empty?
  • How do I go about listing all of the installed packages and versions on mac os x?
  • Insert Columns between columns in excel
  • Laravel lmutator $this->attributes return 'Undefined index: id'
  • Simplify where clause with repeated associated type restrictions
  • How to concat Pandas dataframe columns
  • SQL - Select lowest values with group by and order by?
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Approximate Order-Preserving Huffman Code
  • how to upload multiple files in c# windows application
  • Excel's Macro-Recorder usage
  • Grails calculated field in SQL
  • How to install a .deb file on a jailbroken iphone programmatically?
  • Handling un-mapped Rest path
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • C# - Serializing and deserializing static member
  • Is possible to count alias result on mysql
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Sending data from AppleScript to FileMaker records
  • How to handle AllServersUnavailable Exception
  • Can I make an Android app that runs a web view in Chrome 39?
  • VBA Convert delimiter text file to Excel
  • R: gsub and capture
  • How to include full .NET prerequisite for Wix Burn installer
  • Benchmarking RAM performance - UWP and C#
  • LevelDB C iterator
  • Linking SubReports Without LinkChild/LinkMaster
  • Is it possible to post an object from jquery to bottle.py?
  • reshape alternating columns in less time and using less memory
  • Reading document lines to the user (python)
  • Net Present Value in Excel for Grouped Recurring CF