I have a workbook that users can record both their vacation & sick time.The cell range for the data entry is D5:Q30 on both sheets. This range is for the days of the week, from Sunday through Saturday, covering a 2 week period, & therefore 26 pay periods. Currently you are required to enter the letter "H" for Holiday designation, on 2 sheets.
I am trying to avoid errors by only entering H on one sheet & then use a button that is VBA activated to copy all the Hs. Sheet 1 is named Annual_Leave_Record, Sheet 2 is named Sick_Leave_Record. I have googled, about this, but have not been successful in adapting some of the examples.
Currently I am working on getting the Hs copied, eventually I would like to also ensure that all the Holidays are entered, & using the copy function as a double check. The are 10 federal holidays, I was thinking that if < 10 Hs were entered, the following message would be displayed " Are you certain that you entered all the holidays?". If > 10 Hs were entered, the following message would display "Please check your holiday entries. I found X holiday entries."
Cell range D5:Q30 is conditionally formated, when the cell is < than today it fills with color.
I found VBA that does copy H, but it also copies some of the conditional formatting & I don't know why. I don't want it to copy the conditional formatting. Below is the formula:
Sub Copy_H() Sheets("Annual_Leave_Record").Range("D5:Q30").Copy Destination:=Sheets("Sick_Leave_Record").Range("D5") End Sub
I will continue working on this, but would appreciate any help. Thanks in advance.