Running One Macro in Multiple Worksheets & Loop

[mjali001]

Hey guys I am new on the forum and heard great things about it and how much some people have learned through this forum. I am relatively new to excel vba, and need help figuring out this macro so I'll go straight to the point. Thanks in advance for the help

So I've built a macro for a report that I run every month. I wanted to know how I can run the same macro and in different tabs ( I have a totally of 22 tabs) I didn't want to create 22 macro's for this.

I was also wondering if there is a way I can have this loop, in the sense that once it started in sheet one it can go to sheet 22 and stop.

I've built this macro in the workbook that I am working with and would it be problem to move it to my personal macro workbook?

And finally the ranges differ from sheet to sheet in the macro below the range goes to M5:M200 and A5: O200 but it can go up to M5:M5000 and A5:05000 should I just put in a high value that my macro could work against.

Thank you all so much again for your help and inputs. Below is the macro I've built and trying to finish;

Please Login or Register  to view this content.

[royUK]

You don't need to activate or select. Why have you got 22 sheets if they are all identical?

Also, to hide errors you can use a the worksheet function ISERROR

[mjali001]

The 22 sheets are identical in terms of formating for reporting metrics, but all represent various business and values that are not similiar. Each sheet represent individual businesses along with their respective values, etc..

I tried using ISERROR but it does not remove the #DIV/0!, I need to get rid of #DIV/0! and turn the values to zero for sorting purposes.

[Alf]

To loop through all the sheets you could use something like this:

Please Login or Register  to view this content.

If you always have 22 sheets you could replace “ThisWorkbook.Sheets.Count” with 22.

You can’t replace a “#DIV/0!” as long as it is a formula value. You either use an “If” statement in your formula i.e. “=A2/C3” and if C3 could be 0 you change your formula to “=IF(C3=0,0,A2/C3)” or you copy the formula value i.e ( “#DIV/0!”) back to itself as a value thereby changing a formula to a string value and now you can use “Find” and “Replace”

Alf

[mjali001]

Thank you Alf for your insight. The DIV/0! I can't do much because it is a formula but there are also vlookups which lag the whole report too, thats why I've decided to copy paste the values.

I'm also trying to understand how to do a loop and having some diffcultly since I've never done one, would this be valid

Please Login or Register  to view this content.

[Alf]

You have dim'ed i as a number (integer)

Then you count the numbers of sheets in your workbook.

Please Login or Register  to view this content.

Then you run your macro on the first sheet.

Please Login or Register  to view this content.

then your code goes in here
After processing sheet1 (i goes from 1 to the last sheet in your workbook) you call the next sheet with the line

Please Login or Register  to view this content.

so your code looks like this

Please Login or Register  to view this content.

This is the loop, the value of i changes from 1 to 22 (if there are 22 sheets in your workbook) So it starts by activating sheet(1), when your code has done its workt the comand "Next i" changes i to 2 and sheet(2) gets activated and your code runs through it and so fort untill all sheets are processed by your code.

Perhps it's easier to understand if you know that the the proper term is

Please Login or Register  to view this content.

If the part "Step 1" is omitted excel assumes you do want to increase i with 1 each time you call "Next i".

Alf