How can I access the WorkSheet name within the worksheet to trigger action ?

[ExPatBrit]

I have a worksheet with columns for each Dept; containing Dept-local details of 'across the board' details (eg Dept staff names, equipment issued &c)
I want to consolidate them all back to a front-page Summary for the Company.
Each worksheet will have a Dept Name:
& I do not need them to see columns from other Depts.

Rather than creating a 'custom' worksheet for each Dept, I'd like to keep it generic, as I wish to consolidate (& possibly summate) all individual Dept worksheets into the front-page Summary.

But, to do this, I believe I would need to Hide the columns for other Depts.

Ideas please ?

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

You say you have "a worksheet" but you want to "consolidate them all". Do you have one worksheet or one worksheet per department or something else?

Are you saying that you want to have a summary sheet that you can show everyone without the details on the other worksheet (or worksheets) being visible?

If you have a workbook with details, I would copy the generally viewable summary information into another woprkbook using a report macro in the master workbook. Basically, you would maintain the master workbook with the details and run a macro when you need to update the summary workbook. You could do that automatically when the master is changed but you should allow for the summary workbook being in use when you want to update it.