Running macro on certain worksheets

**chunte** · 06-21-2009, 02:09 PM

Hi all,

I'm looking for a way to prevent a macro from being run on certain worksheets. In my workbook, there different types of sheets that I have made, for example Buying, Selling, etc...

Say I want to have restrict a macro to run only on the Buying worksheets, is there a way to do this in code. Ideally, I want code it in such a way that it is transparent to the user, for example a CustomProperty or a variable within the code for a sheet that I can look up.

Many thanks in advance.

Best regards,
Chun Te

**Leith Ross** · 06-21-2009, 02:31 PM

Hello Chunte,

Welcome to the Forum!

There a couple of ways to do this. It would really help to see your code. We could then tell you the best method to use. Posting your workbook would be best.

**chunte** · 06-21-2009, 03:01 PM

Thanks for the quick response!

For the piece of functionality that I'm thinking about, I've yet to make any code yet and still in the planning phase, so I don't think it is worth sharing the code.

Essentially, there will be a macro that aggregates data from multiple sheets and places them into a new sheet. Which sheets are aggregated is determined by the User. I intend to have a TextBox where the User can list the sheetnames they want. I hope to then validate the list based on their types (e.g. 'Buying', 'Selling') as the macro would only work on certain type of sheets/templates.

I could have a cell in the worksheet, e.g. A1, fixed with the value 'Buying' but I hope to not rely on that as the User might want to change the value of that cell. Hence, ideally I'd like to be able to code it into the sheet.

Many many thanks....

Chun Te

**Leith Ross** · 06-21-2009, 03:06 PM

Hello Chunte,

The easiest method is to have the macro check the ActiveSheet's name. If it is not the sheet you want then exit the macro. Use this as the first line in the macro.

Code:

    If ActiveSheet.Name <> "Buying" Then Exit Sub

**chunte** · 06-21-2009, 03:15 PM

Hi Leith,

Hmm... That's right, but wouldn't that would work only if the name of the sheet is 'Buying'. The sheets would have different names to correspond to a different person and none of the sheets have the word 'Buying' in them.

Best regards,
Chun Te

**royUK** · 06-21-2009, 03:21 PM

I think you are working backwards. Al data is best stored on one sheet, with a field for the names.

Then yo can use PivotTables to work with the data