smashclash
Board Regular
- Joined
- Nov 24, 2003
- Messages
- 126
- Office Version
- 365
- Platform
- 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?
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?
