Forum Discussion
Lavalips
Jun 22, 2021Copper Contributor
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?
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(...)
Should the row be copied to a fixed location (if so, which location), or to the first empty row on the other sheet, or ...?
- LavalipsCopper Contributor
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
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.