Is there a way to have users copy and range of cells and paste them to another range without copying the formulas in a protected worksheet. I want them to only copy and paste the values. I know there is a values only button on the paste tab, but the people using this are not so savvy. I am trying to idiot proof it.
if the ranges you are copying to and from are always the same, you could record a macro, and add a button for them to use?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I don't know how to do macros.
It is a month long report that is added to twice a week. The information is generated in the same spot for each sales report then my intention is to copy the values from this range and paste into another section that adds together for mothly totals. Here is an example below, don't know how it will come out on the post. The information from today will be posted into report 1, report 2, etc.
TODAY THIS MONTH Sales Classification Report 1 Report 2 Report 3
Sales Cost Sales Cost Today This Month Sales Cost Sales Cost Sales Cost
0.00 0.00 0.00 0.00 New 0.00
0.00 0.00 0.00 0.00 Add 0.00
0.00 0.00 0.00 0.00 Re-open 0.00
0.00 0.00 0.00 0.00 Total 0.00 0.00
0.00 0.00 0.00 0.00 Non-Taxable
0.00 0.00 0.00 0.00 Today This Month
0.00 0.00 0.00 Cash 0.00
0.00 0.00 0.00 Inst. 0.00
0.00 0.00 Mark-up #DIV/0! #DIV/0!
0.00 0.00 0.00 0.00 Mark-up w/o Del #DIV/0! #DIV/0!
it would be esier to see what you need if you upload a sample workbook
Attached is an example of what i want to do. I am using a company version of Excel 2010 and do not have access to the all the functions for the cut/copy/paste section under File/Options/Adavanced options to choose how to paste texts that I have seen in other articles about this. What I want to do is copy the values only from the yellow highlighted areas to the Report 1, Report 2 etc. in the blue areas.
Assuming that the file you had uploaded does not require any modifications like adding rows / columns, the below macro will be able to copy paste the values from columns C, D into columns J:U. This will unprotect the worksheet, copy paste values into the destination cells and again re-protect the sheet:
To copy this macro into your workshet, follow the following steps:
Sub Copy_Pate_Values() Sheet1.Unprotect Password:="abcd" 'Replace abcd with your password Range("C4:D4").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("C4").Offset(0, 7).Select Selection.PasteSpecial Paste:=xlPasteValues Range("C4").Offset(0, 9).Select Selection.PasteSpecial Paste:=xlPasteValues Range("C4").Offset(0, 11).Select Selection.PasteSpecial Paste:=xlPasteValues Range("C4").Offset(0, 13).Select Selection.PasteSpecial Paste:=xlPasteValues Range("C4").Offset(0, 15).Select Selection.PasteSpecial Paste:=xlPasteValues Range("C4").Offset(0, 17).Select Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Sheet1.Protect Password:="abcd" 'Replace abcd with your password End Sub
1. Press Alt + F11 while you are on the workbook
2. Double click on "ThisWorkbook"
3. Paste the above macro
Now press F5 to run the macro or on your excel file type Alt + T + M + M, then select "ThisWorkbook.Copy_Pate_Values" and click on Run
Last edited by FDibbins; 09-28-2012 at 10:16 PM.
If solved kindly remember to mark Thread as solved.
Click the small star icon at the bottom left of my post if this was useful.
If you use the following it should be fine.
Sub Paste_Special() Sheet1.Unprotect Password:="Password" (replace with the password you want) Range("C4:D13").Select Selection.Copy Range("L4").End(xlToRight).Offset(0, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheet1.Protect Password:="Password" (Same as above) End Sub
Regards
Danny
Thanks for the help. This report is being used twice a week throughout the month. so when we are taking the information from the yellow and pasting it into the blue we are doing report 1 on the first report we do then report 2 the second time in the week, then report 3 the third time we do the report on to report 10. The This month, between the yellow and the blue, totals the numbers placed into the blue sections with each report to give us a running total for the month.
Sorry, I am not clear, are you asking for more help or just letting us know what the sheet is for?Originally Posted by YMGR
@ kbkumar. please note, you need to enclose your code with code tags. i have done it for you this time, but please keep that in mind in future
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks