• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying certain text from 1 sheet to another.

Frncis

Member
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:
Code:
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.​
 
Last edited:
Hi,

See if this works:

Code:
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,
 
Hi,

See if this works:

Code:
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")
 
Before I saw you response Khalid NGO. I worked on this piece of code.
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.
 
Last edited:
Before I saw you response Khalid NGO. I worked on this piece of code.
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?
 
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,
 

Attachments

  • Conditional formatting VBA.xlsm
    20.5 KB · Views: 2
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.
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
 
Back
Top