Macro Suggestion?

smashclash

Board Regular
Joined
Nov 24, 2003
Messages
126
Office Version
  1. 365
Platform
  1. Windows
I have a file that has 13 sheets on it. the first is labeld budget info, and the remaining 12 are each month of the year... jan...dec. On each month tab, there are cell references to information on the budget info tab. Once the variable information is put on each month's sheet it is compared against the budget information on each month sheet. (the budget information is linked to the budget info tab.)

Once each month is completed these reports are published on a report-to-web system. The directors of other departments are then able to go in an view this information. They have the potential to save the file in the excel form it is created in. The problem with this is that I do not want the directors to have the ability to view the budget info tab and month's September - December. I can hide those months so they do not see them, but I do not want them to even have the option of unhiding the sheets to view the information.

I've thought this over and here are the options I came up with:

1) Go into each file and move Sept - Dec sheets to another file, move any information on the budget info sheet related to Sept - Dec to a separate file. (This will be very time consuming since there are around 100 workbooks). Also, this will not give me a file where I can view all the information for the entire year unless the original file is saved.

2) Copy a month sheet in a workbook and save it is as another file. Then paste values the information so the file will not need any links. (This is acceptable, however it would be time consuming because you would have to save each file as whatever the dept name is. Unless there is a way to have the file save as what is written in a cell? Also, I want to keep prior months information in the file and not overwrite the old file. Meaning, I want to be able to have January and February in the same workbook but on different sheets.) Is there a way to write a macro that could copy the a month worksheet (perhaps, the macro could ask which month to copy) paste values the values in another workbook and label the new workbook with whatever the department name is?

3) Anyone have a better idea?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

smashclash

Board Regular
Joined
Nov 24, 2003
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Is there a way password protect individual sheets within a workbook? However, I would only want to be prompted for the password if I tried to unhide them. Meaning, if I simply open the file I would not want to be asked for a password.
 

GaryB

Active Member
Joined
Feb 25, 2002
Messages
459
Could you use VBA to set the pages to VeryHidden? They can't then be unhidden from Excel, only VBA, and they don't even appear in the unhide dropdowns. Ok, it's not very secure but unless one of the directors goes into VB editor he won't know the sheets exist

HTH

GaryB
 

smashclash

Board Regular
Joined
Nov 24, 2003
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Yes, I did a search and found a good post about the very hidden feature. This is great. Thanks, and sorry I didn't do the search initially, I just couldn't think of what to search for.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,101
Messages
5,768,100
Members
425,453
Latest member
bince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top