How to run the same macro on multiple worksheets at same time in Excel?
Normally, we can run a macro in a worksheet, if there are multiple sheets need to apply this same macro, you should trigger the code one by one sheet. If there any other quick way to run the same macro on multiple worksheets at once in Excel?
Remove all macros from multiple workbooks:
Kutools for Excel's Batch Remove All Macros utility can help you to remove all macros from multiple workbooks as you need. Download and free trial Kutools for Excel now!
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
To run a macro on multiple sheets at same time without triggering it one by one sheet, you can apply the following VBA code, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Module Window.
VBA code: Run the same macro on multiple worksheets at same time:
Sub Dosomething() Dim xSh As Worksheet Application.ScreenUpdating = False For Each xSh In Worksheets xSh.Select Call RunCode Next Application.ScreenUpdating = True End Sub Sub RunCode() 'your code here End Sub
Note: In the above code, please copy and paste your own code without the Sub heading and End Sub footer between the Sub Runcode() and End Sub scripts. See screenshot:
3. Then put the cursor at the first part macro, and press F5 key to run the code, and your macro code will be applied to one by one sheet.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months ago1004 runtime error
To post as a guest, your comment is unpublished.· 4 months agoSub Dosomething()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
Application.ScreenUpdating = True
.EnableOutlining = True
.EnableSelection = xlNoRestrictions
.Protect Password:="workana1234", _
Hola! he seguido las pautras pero me señala como error la fila xSh.Select.
Al dar F5 me indica que se ha producido un error 1004 en tiempo de ejecucion.
Error en el metodo "Select" de Objeto "_Worksheet"