VBA Code to search a string and if the string is found paste in another worksheet

Question

I am trying to write a VBA code where there is string mentioned in a cell(search_string as per the code). I search for that string in another Worksheet(MasterRolePLMap) and if the string matches I have to copy the entire row and paste it another worksheet(Comptetency View) . When I try to paste the value there is debug error as "To Copy All cells from one worksheet to another start from R1c1"

below is my code

Sub Click_1()
    Dim key As String
    Dim size As Integer
    row_number = 0
    search_string = Sheets("CompetencyView").Range("B5")
    Sheets("CompetencyView").Activate
    Sheets("CompetencyView").Unprotect Password = "ritu"
    Sheets("CompetencyView").Range("A12").Select
    Do
        If IsEmpty(ActiveCell) = False Then
            Range(ActiveCell, ActiveCell.Offset(0, 11)).Delete shift:=xlUp
        End If
    Loop Until IsEmpty(ActiveCell) = True     'Clearing the previous data
    lastrow = Sheets("MasterRolePLMap").Cells(Rows.Count, 1).End(xlUp).Row    'No of rows in MasterRolePLMap
    Do
        row_number = row_number + 1
        Comp_Name = Sheets("MasterRolePLMap").Range("A" & row_number)
        If InStr(Comp_Name, search_string) > 0 Then
            Sheets("MasterRolePLMap").Rows.Copy
            Sheets("CompetencyView").Activate
            Sheets("CompetencyView").Range("A1").Select
            ActiveSheet.Paste
            Sheets("MasterRolePLMap").Activate
        End If
    Loop Until Comp_Name = ""
End Sub

try not to put mountains of blank lines in your code, there is never a need to put 5 blank rows throughout the code, it makes it extremely large on the screen and harder to read. — Dan Donoghue
You are pasting to a ActiveSheet when you need to be pasting to a Range. Also, your code can be, and should be, amended to get rid of every instance of Activate, ActiveSheet, & Select. See here for deets — urdearboy

Dan Donoghue Dan Donoghue · Accepted Answer · 2020-02-26T01:17:18

There was a lot of code in here that didn't need to be, you defined variables and never used them then you used variables that you never defined.

I have cleaned it up as best I can but without your data to test on I don't know if it will work.

Try this and report back:

Sub Click_1()
    Dim Comp_Name As String, search_string As String, row_number As Long
    row_number = 0
    search_string = Sheets("CompetencyView").Range("B5")
    Sheets("CompetencyView").Activate
    Sheets("CompetencyView").Unprotect Password = "ritu"
    Sheets("CompetencyView").Range("A12:A" & Range("A" & Rows.Count).End(xlUp).Row).Delete shift:=xlUp
    Do
        row_number = row_number + 1
        Comp_Name = Sheets("MasterRolePLMap").Range("A" & row_number)
        If InStr(Comp_Name, search_string) > 0 Then
            Sheets("MasterRolePLMap").Rows(row_number).Copy 'Changed this to copy the row you need
            Sheets("CompetencyView").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Paste 'I don't think you want to always paste to row 1 as you will just keep overwriting it so I changed it to offset the existing data
        End If
    Loop Until Comp_Name = ""
End Sub

VBA Code to search a string and if the string is found paste in another worksheet

1 Answers