More Excel help please
fat controller
Posts: 13,757 Forum Member
✭✭
I have a fairly complex spreadsheet, which is used to collate date across a 28 day period; at the end of each 7 day period, there is a summary page which totals up all the data for the week at the top half of the page, and then has a number of cells at the bottom half of the page (comments text, time, quantity, cost 1, cost 2) x 10 lines for each day.
I need to lock all the cells on the page to prevent users from knackering the formulae in there, however in doing so I am also preventing a few people in head office from being able to select the cells to copy the information in them and then pasting that information into a word document or a blank workbook.
At the moment, I have given the select few the password to remove the protection, however this is less than ideal - so, is there a way that I can allow them to select a set of cells to copy the information (not the formulas) in them to then be pasted into another workbook?
Or, is there a way to have a macro output the information from those cells directly into a new/blank workbook that the user can then copy and paste what they want?
Thanks in advance.
I need to lock all the cells on the page to prevent users from knackering the formulae in there, however in doing so I am also preventing a few people in head office from being able to select the cells to copy the information in them and then pasting that information into a word document or a blank workbook.
At the moment, I have given the select few the password to remove the protection, however this is less than ideal - so, is there a way that I can allow them to select a set of cells to copy the information (not the formulas) in them to then be pasted into another workbook?
Or, is there a way to have a macro output the information from those cells directly into a new/blank workbook that the user can then copy and paste what they want?
Thanks in advance.
0
Comments
Sub Output_Macro()
'
' Output_Macro Macro
'
' Keyboard Shortcut: Ctrl+o
'
ActiveSheet.Unprotect Password:="XXXXXXX"
ActiveWindow.SmallScroll Down:=60
Range("A64:M154").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.WindowState = xlMinimized
Application.CutCopyMode = False
ActiveSheet.Protect Password:="XXXXXXX", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
This outputs the values only to a new workbook which I hope will be sufficient for the onward copy/paste operation.
Thanks
Not sure if this is what you want to achieve, and also not sure of which version of Excel you're using, but you can copy what you want from the spreadsheet and instead of doing "Paste", in the Excel 2000 that I use you can do a "Paste Special" and from there select "Values" which pastes the result of any formula but not the formula itself. However this will not copy any formating to the target cell which may affect things.
Thanks - I am currently using Office 2010, but it has to be an Office 2003 file as many of the PC's at work are somewhat antiquated.
I will have to ask on Monday to see if this solves the problem, and if not it will be back to the drawing board.
The best of it is, this spreadsheet is used by a considerable number of people, and does exactly what we need of it, however there is a small group who need to be able to copy and paste some of the values onward to other docs/sheets. As mentioned above, they currently have the password to open and relock the spreadsheet, but as this is getting rolled out to a wider and wider audience, I am getting all the more jittery that someone will bend it out of shape and then I will be spending all my time fixing the various copies of the sheet being used - hence the desire to lock it completely.
Would everyone have access to that macro you've written above? If it contains the actual password then it doesn't look to be very secure - is this an issue?
In database terms you need a view of the source data. There are probably better ways of achieving this but why not create a new workbook and make each cell a reference to the correspondin cell in the original workbook. I've done that before, extending the reference to include another workbook name along with the worksheet name and cell reference. It'll take a bit of effort to create it but you can then allow access to the vew without affecting source data. Probably.
I'll pop back tomorrow to see how barmy this idea probably is.
Eit - I see Mr Smiley above has suggested something similar. Neat.