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

Solved
Report
Posts
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 5, 2021
-
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
-
Hi,

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.



Regards,
Raj

4 replies

Posts
3179
Registration date
Monday November 25, 2019
Status
Administrator
Last seen
October 7, 2021
6,758
Hello, we do have some helpful Excel experts on the forum and hopefully they can offer a more informed response as to whether or not there is an automatic way of doing so...

Whilst not automatic, you could organise the "Status" column on sheet 1 into alphabetical order - separating the No's from the Yes', and then transferring in bulk all of the relevant Yes values manually into sheet 2.

Regards
Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
Hello Raj,

Further to David's suggestion, you could try the following VBA code:-

Sub Test()

Application.ScreenUpdating = False

        Sheet2.ListObjects("Table2").Unlist '---->Use your actual table name.

        With Sheet1.ListObjects("Table1").DataBodyRange  '---->Use your actual table name.
                .AutoFilter 4, "Yes"
                .Offset(1).Resize(.Rows.Count, .Columns.Count).Rows.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With
        
        Sheet2.Select
        Sheet2.ListObjects.Add(xlSrcRange, Cells(1, 1).CurrentRegion, , xlYes).Name = "Table2" '---->Use your actual table name.
        Sheet1.Select
        
Application.ScreenUpdating = True

End Sub


You'll need to change the table names to your actual ones.

I hope that this helps.

Cheerio,
vcoolio.
2
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

Posts
1326
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 28, 2021
241
You're welcome Raj. I'm glad to have been able to assist.

Cheerio,
vcoolio.
Posts
31
Registration date
Wednesday August 26, 2020
Status
Member
Last seen
August 5, 2021

Hi vcoolio,

It's working as per my expectation.

Thanks a lot and keep rocking.

Regards,
Raj