1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'VBA Macros' started by Frncis, Nov 8, 2018.

  1. Frncis

    Frncis Member

    Messages:
    56
    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 (vb):
    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: Nov 8, 2018
  2. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,952
    Hi,

    See if this works:

    Code (vb):

    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,
  3. Frncis

    Frncis Member

    Messages:
    56
    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")
  4. Frncis

    Frncis Member

    Messages:
    56
    Before I saw you response Khalid NGO. I worked on this piece of code.
    Code (vb):
    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: Nov 8, 2018
  5. Frncis

    Frncis Member

    Messages:
    56
    Is it possible to even include the countif function in this copy command, or should I place a call command after the copy code?
  6. Khalid NGO

    Khalid NGO Excel Ninja

    Messages:
    1,952
    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,

    Attached Files:

  7. Frncis

    Frncis Member

    Messages:
    56
    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 (vb):
    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
     

Share This Page