• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automate a macro across multiple sheets

Learner85

New Member
Hello, I have been improving my vba skills - slowly! I have macros that works fine on each active sheet but how can I run these across all sheets or selected sheets

There are four macros, one macro just brings all the sheets to be the same start row and column data this is a one time run, two macros remove rows based on specific cell data and one removes negative numbers these last three I want to run over multiple sheets.

Any help would be very welcome

Regards
 

Attachments

  • VBA Request.xlsm
    65.8 KB · Views: 10
Learner, I opened this workbook but I don't see any code in it; the VBA editor looks empty of modules. Did I do something wrong, or ...?

Generally speaking if you want to run a VBA program against more than one worksheet I can think of two ways easily. (There are probably more. There always are.)

1) If you want to perform the tasks on every worksheet, or if your program can tell by looking at the worksheet whether it's one it should work on, you can loop through all the worksheets and do whatever it is you want inside the loop. It might look something like this:
Code:
'owb = your workbook
For Each ows In owb.Worksheets

  ' If you want to ignore some worksheets, perform the check here.  For example:
  If Left(ows.Name, 11) <> "Mitigation " Then Goto IterateSheet
  
  ' Perform the tasks on the worksheet here.

IterateSheet:
  Next ows
This looks at all the worksheets in your workbook, skips any that aren't named "Mitigation something", and does whatever you want to the rest.

2) If your program can't tell by looking at the worksheet—if only you can tell it—you can try it this way:
Code:
'owb = your workbook
DoYourThing "Full"
DoYourThing "Front"
DoYourThing "Rear"
Exit Sub

Sub DoYourThing(SheetName)
  Set ows = owb.Worksheets(SheetName)
  ' Put your code here to do whatever it is you want to do to ows
  End Sub
In this case you'll have to find a way to tell DoYourThing what owb is, but that's a detail. You can define it as Public, or pass it as an argument.

Is that any help?
 
if you could review the code in the file now it is the correct one and advise, I would appreciate it.
 
Last edited:
Back
Top