How to copy cell value from one table to another table with Criteria

[RajG]

Hi,
I am new to VBA, please help me with the below request.

I would like to Copy the data from table1 (sheet1) to table2 (sheet2) based on the criteria "Yes" automatically or some action.

The table 1 is located in Sheet 1 and table2 is located in Sheet2.

For clear understanding I have attached the screenshot as below.

Thanks in advance,
Raj

 

[vmjan02]

try this

sub copypaste()
Sheets("sheet1").Select  ' can change the sheet name
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With ActiveSheet
    .AutoFilterMode = False
    .Range("A1:D" & LR).AutoFilter Field:=4, Criteria1:="Yes"   ' consider the column D
End With

'Sheets("sheet2").select  ' can change the sheet name
Dim LastrowC1 As Long
LastrowC1 = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Dim LastrowC As Long
LastrowC = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("sheet1").Range("A2:D" & LastrowC1).Copy Sheets("sheet2").Cells(LastrowC, 1)
LastrowC = Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1

End sub



[CODE=vba]

 

[RajG]

Hi VM,

Thanks for your prompt response!

The Table 1 value is copied to the table 2 from the bottom instead of top to bottom and when copy the value from table 1 the table rest of the value is hidden. I would like to show all the value in table 1. Then "Yes" value only moves to table 2 from the top.

Thanks in advance.

Regards,
Raj/

 

[vmjan02]

just add this lines before end sub

sheets ("sheet1").select
With ActiveSheet
    .AutoFilterMode = False
End With
End sub

and regarding copy Paste, it will past in sheet 2 in last blank cells in column A.

 

[RajG]

Thanks Vm,

It's Working perfect.

 

[vmjan02]

Great,
Thank you for sharing feed back