excel vba specific row copy paste by cell referance

Question

I have to select specific row from so many rows and copy paste to another sheet but I don't want to specified value of cell if it takes value from particular cell
my code is

Sub Macro3()
    Dim rngG As Range
    Dim cell As Range
    Sheets("Urban-Ward_vise").Select
    Set rngG = Range("G1", Range("G65536").End(xlUp))
    For Each cell In rngG
        If cell.Value = "BAVLA" Then
            cell.EntireRow.Copy
            Sheets("Sheet1").Select
            ActiveCell.Select
            ActiveCell.Offset(1, 0).Range("A1").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End If
    Next cell
    Selection.End(xlUp).Select

End Sub

I want to select rows from Urban-Ward_vise and paste in sheet1

in

If cell.Value = "BAVLA" Then

I don't want put value "BAVLA" but I want to read the value from sheet1 cell like c5 so please help

score 0 · Accepted Answer · answered Apr 18 '19 at 10:55

0

Do you mean something like this?

It would be worth you reading how to avoid Select.

Sub Macro3()

Dim rngG As Range
Dim cell As Range

With Sheets("Urban-Ward_vise")
    Set rngG = .Range("G1", .Range("G" & Rows.Count).End(xlUp))
End With

For Each cell In rngG
    If cell.Value = Sheets("Sheet1").Range("C5").Value Then 'specify sheet name and cell here
        cell.EntireRow.Copy
        Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    End If
Next cell

End Sub

answered Apr 18 '19 at 10:55

SJR

22,986
6
18
26

thanks for help and if there is Urban-Ward_vise and Sheet1 if there is no match so how to add do nothing in code – bhagvat dave Apr 18 '19 at 11:31
Not sure I follow. Currently the code only does something if the column G value matches C5. – SJR Apr 18 '19 at 11:35

excel vba specific row copy paste by cell referance

1 Answers1