Copying certain text from 1 sheet to another.

[Frncis]

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.​

 

[Khalid NGO]

Hi,

See if this works:

Sub Copy_H()
Sheets("Annual_Leave_Record").Range("D5:Q30").Copy Destination:=Sheets("Sick_Leave_Record").Range("D5")
ThisWorkbook.Sheets("Sick_Leave_Record").Activate
Range("D5:Q30").Select
Selection.FormatConditions.Delete
End Sub

VBA is not my cup of tea.

Regards,

 

[Frncis]

Hi,

See if this works:

Sub Copy_H()
Sheets("Annual_Leave_Record").Range("D5:Q30").Copy Destination:=Sheets("Sick_Leave_Record").Range("D5")
Application.Goto Sheets("Sick_Leave_Record").Range("A1")
Range("D5:Q30").Select
Selection.FormatConditions.Delete
End Sub

VBA is not my cup of tea.

Regards,

I get a Run-Time error "9" script out of range message on
Sheets("Annual_Leave_Record").Range("D5:Q30").Copy Destination:=Sheets("Sick_Leave_Record").Range("D5")

 

[Frncis]

Before I saw you response Khalid NGO. I worked on this piece of code.

Sub Copy_H()
Sheets("Annual Leave Record").Range("D5:Q30").Copy
Sheets("Sick Leave Record").Range("D5:Q30").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub

It does copy all appropriate data & does not change any of the Conditional formatting.
I used your code as a starting point.
Now I have to figure out how to count all the Hs that were entered & include the appropriate user message for entries <10 & > 10.

 

[Frncis]

Before I saw you response Khalid NGO. I worked on this piece of code.

Sub Copy_H()
Sheets("Annual Leave Record").Range("D5:Q30").Copy
Sheets("Sick Leave Record").Range("D5:Q30").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub

It does copy all appropriate data & does not change any of the Conditional formatting.
I used your code as a starting point.
Now I have to figure out how to count all the Hs that were entered & include the appropriate user message for entries <10 & > 10.

Is it possible to even include the countif function in this copy command, or should I place a call command after the copy code?

 

[Khalid NGO]

I get a Run-Time error "9" script out of range message on
Sheets("Annual_Leave_Record").Range("D5:Q30").Copy Destination:=Sheets("Sick_Leave_Record").Range("D5")

Hi,

I have slightly modified the code and tested with random data (as per attached)

Wait for vba experts, if it still doesn't work.

Regards,

 

[Frncis]

Is it possible to even include the countif function in this copy command, or should I place a call command after the copy code?

With help of friends I have code that copies & checks the number of entries for the number of occurrences of H. I had to call a count macro from the copy macro.
Below is the entire code.

Sub Copy_H()
Sheets("Annual Leave Record").Range("D5:Q30").Copy
Sheets("Sick Leave Record").Range("D5:Q30").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Call Count_H
End Sub
Sub Count_H()
Dim instances As Long
    Dim strMsg As String
    instances = WorksheetFunction.CountIf(Worksheets("Annual Leave Record").Columns("D:Q"), "H")
    If instances = 10 Then
      strMsg = "You have entered all Federal Holidays."
    ElseIf instances < 10 Then
        strMsg = "Are you certain that you entered all the holidays? I found " & instances & " holiday entries!"
    Else
        strMsg = "Please check your holiday entries. I found " & instances & " holiday entries!"
    End If
    MsgBox strMsg, vbExclamation, "VA Leave Record"
End Sub