I have a document that contains 31 workssheets. Data that has been input into some of the cells is copyed and pasted from one sheet to another on a daily basis. The problem I have is that when they copy and past this data they do not use the paste special function and choose value only so it is creating redundent Conditional Formatting rules. What can I do in order to prevent this from occuring. The end users are not experienced in Excel, thanks
So, just to be clear...they are allowed to copy and paste, just no formatting?
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
They are allowed to copy and paste, yes. They hav full right to the worksheet; basically what they do is enter data on one sheeet then at the end of the day they carry some of it to another sheet for the next day. When they copy and paste a1 from sheet1 to sheet2 it make an additional conditional formating rule on sheet2. It gets componded when you are talking about 31 sheets.
Well, the EASIEST way, is when they copy. then the option box shows up on the the paste, select no formatting...,but this sounds like you want to turn the formatting option off altogether...sounds like a VBA solution again...there is NO way to turn the copy/paste options off in Excel formulas...
Would it be possilbe for you to guide or give me a way to use a vba to prevent this from happening. I am not an expert but i dabble in vba some. It didnt think i have have any other solution other than to use vba. Thanks for clarifying that for me.
Better ask the users to paste it as values
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Well. it is definitely beyond my VBA skills as well, but, I will see if someone else can answer it for you...
Thank you. It will be appreciated.
This is actually a pretty simple macro. The hard part will be installing it and attaching a shortcut key to it to do what you want. For instance, you could intercept the CTRL-V for paste function... or you can assign a unique hotkey and teach your people to paste it using that hotkey instead of the CTRL-V.
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
I found a similar thread but I do not know enough to convert it to my situation. The difference is that if column I1 on my sheet = the word complete then the macro would shade the cells in row A1 to I1 to yellow. Basically if i type the word complete or use a drop down list to select the work complete in any cell in the column I it will shade the cells in that row from A1 to I1 yellow.
Here is the thread info:
If column B row whatever = todays date then I want the row from col B to col O, to fill in green. I know I can do this with conditional formatting but I have data that updates itself which causes this conditional formatting to go funny every day, so I really need some sort of macro to do it.
Option Explicit
Sub HighlightIfDateIsToday()
Dim rangeToTest As Range
Dim cell As Range
Dim dateToTest As Date
Dim lrow As Long
lrow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
'MsgBox lrow
Set rangeToTest = Worksheets("Sheet1").Range("B1:B" & lrow)
dateToTest = Date
For Each cell In rangeToTest
If cell.Value = dateToTest Then
Range(cell, cell.Offset(0, 13)).Interior.Color = 5287936
End If
Next cell
End Sub
Last edited by jcmoody1993; 05-13-2013 at 06:17 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks