Copy entire row if letter “R” is entered in the row.

Boboka

New Member
Joined
Jan 19, 2022
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
Hi everyone,

I tried to search on the internet but can’t find a solution. I am listing employees out from the same department using vlookup.

In the end 10 rows of employees data are shown from Column A : G. I want to copy that row of data if I type a letter “R” in column H. Column A is hardcoded numeric 1,2,3 etc. where column B : G is vlookup formula data.

This row of data will then appear in a new sheet call Leader in 1st row.
So when I vlookup another department, maybe another 8 row of employee data are shown. So when I type a letter “R”, in colum H, one of the employee row. It will copy that row in Sheet Leader under 2nd row. I found this code in the internet and try to edit.

VBA Code:
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 "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

This can’t work as it can’t paste a vlookup formula data into sheets Leader. It only copy hardcode numeric no. If I type a “R” on row 10 employee, it only show 10. But the column B to G is blank. And it appear in sheet Leader A10 cell “10”.

Thank you in advance..
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

rollis13

Well-known Member
Joined
Jul 30, 2012
Messages
1,010
Office Version
  1. 2016
Platform
  1. Windows
If I correctly understood, have a try with my macro to be pasted in the sheet's module:
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsh    As Worksheet
    Dim LR     As Long
    If Not Intersect(Range("H10:H" & Range("H" & Rows.Count).End(xlUp).Row), Target) Is Nothing Then
        Set wsh = Worksheets("Leader")
        LR = wsh.Cells(Rows.Count, 1).End(xlUp).Offset(Abs(wsh.Cells(Rows.Count, 1).End(xlUp).Value <> ""), 0).Row
        If UCase(Target) = "R" Then Cells(Target.Row, 1).EntireRow.Copy
        wsh.Range("A" & LR).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End If
End Sub
 

Boboka

New Member
Joined
Jan 19, 2022
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
If I correctly understood, have a try with my macro to be pasted in the sheet's module:
VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsh    As Worksheet
    Dim LR     As Long
    If Not Intersect(Range("H10:H" & Range("H" & Rows.Count).End(xlUp).Row), Target) Is Nothing Then
        Set wsh = Worksheets("Leader")
        LR = wsh.Cells(Rows.Count, 1).End(xlUp).Offset(Abs(wsh.Cells(Rows.Count, 1).End(xlUp).Value <> ""), 0).Row
        If UCase(Target) = "R" Then Cells(Target.Row, 1).EntireRow.Copy
        wsh.Range("A" & LR).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
    End If
End Sub
Thank you, rollis13. This work’s perfectly.
 

rollis13

Well-known Member
Joined
Jul 30, 2012
Messages
1,010
Office Version
  1. 2016
Platform
  1. Windows
Thanks for the positive feedback(y), glad having been of some help.
 

Forum statistics

Threads
1,175,496
Messages
5,897,753
Members
434,675
Latest member
AmitSatnalika

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top