0

I have code that references a sheet "Cost Detailing 1" multiple times, in a workbook which has 25 "Cost Detailing #" sheets. Right now I have 25 separate macros with the only difference being the sheet name referenced. The process right now is to go onto the desired sheet and click a button to update. Is there a way I could set the active sheet that needs populating at the start of the code as a constant so that I only need one macro which will work for any of the sheets?

Thank you

braX
  • 11,506
  • 5
  • 20
  • 33
Robert
  • 1
  • 6
    You should pass the sheet object or name to the routine as an argument – Rory Jan 09 '18 at 17:08
  • 1
    If the button triggering the code is on the sheet to be processed, then using `ActiveSheet` in place of (eg) `Worksheets("Cost Detailing 1")`would likely be safe enough. – Tim Williams Jan 09 '18 at 17:13
  • 1
    Rory is correct. To pass an argument to another sub, the other sub would require you add arguments, such as `Sub Test(ws As Worksheet)`, then you would call that like: `Test myWs` or `Call Test(myWs)`. Another option is to use [Global Variables](https://stackoverflow.com/a/2722160/5781745), but I would use Rory's option before getting to that point. – K.Dᴀᴠɪs Jan 09 '18 at 17:30

0 Answers0