Forum Discussion

Lavalips's avatar
Lavalips
Copper Contributor
Jun 22, 2021

Copy entire row to another sheet when either one of two criteria is met

hi there, my boss has asked me to write a vba to copy and entire row from one excel sheet to another when she either puts either an M or an R into column H and im really struggling 😞 can you help?

  • Lavalips 

    It's my fault - sorry about that.

    I used "NonComformitySchedule" in the code instead of "NonConformitySchedule". Please change this in the line Set wsh = Worksheets(...)

  • Lavalips 

    Should the row be copied to a fixed location (if so, which location), or to the first empty row on the other sheet, or ...?

    • Lavalips's avatar
      Lavalips
      Copper Contributor

      HansVogelaar 

      Hi Hans, Firstly, thank you so much for your reply.

       

      The data rows on source sheet "AuditChecklist" start at A10 and need to be copied to Rows starting at A10 on Recipient sheet "NonConformitySchedule".

       

      The criteria column is H for either M or R entry.

       

      **Note: column H will have several entries but im only interested in copying rows with either M or R entered into column H.

       

      Hopefully the entire rows worth of data will be copied to the recipient sheet based on either M or R entry into column H.

       

      I cannot thank you enough, I hope you can help

       

      Richard

      • Lavalips 

        Right-click the sheet tab of AuditChecklist.

        Select 'View Code' from the context menu.

        Copy the following code into the worksheet module (Edited to correct typo😞

         

        Private Sub Worksheet_Change(ByVal Target As Range)
            Dim wsh As Worksheet
            Dim rng As Range
            If Not Intersect(Range("H10:H" & Rows.Count), Target) Is Nothing Then
                Set wsh = Worksheets("NonConformitySchedule")
                For Each rng In Intersect(Range("H10:H" & Rows.Count), Target)
                    Select Case rng.Value
                        Case "M", "R"
                            rng.EntireRow.Copy Destination:=wsh.Range("A" & rng.Row)
                        Case Else
                            ' Do nothing
                    End Select
                Next rng
                Application.CutCopyMode = False
            End If
        End Sub

         

        Switch back to Excel.

        Save the workbook as a macro-enabled workbook (*.xlsm) and make sure that you allow macros when you open it.

Resources