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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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.
 
Upvote 0

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
 
Upvote 0

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.
 
Upvote 0

Forum statistics

Threads
1,186,911
Messages
5,960,543
Members
438,483
Latest member
Shahin Jack

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