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

Solved/Closed
Raj_1562 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022 - May 4, 2021 at 09:49 PM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - May 5, 2021 at 09:09 PM
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
Related:

4 responses

David Webb Posts 3177 Registration date Monday November 25, 2019 Status Administrator Last seen May 15, 2023   6,927
May 5, 2021 at 03:51 AM
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
David Webb

Content Editor and Community Manager - CCM.net

2
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
May 5, 2021 at 06:56 AM
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
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
May 5, 2021 at 09:09 PM
You're welcome Raj. I'm glad to have been able to assist.

Cheerio,
vcoolio.
1
Raj_1562 Posts 29 Registration date Wednesday August 26, 2020 Status Member Last seen August 29, 2022
May 5, 2021 at 11:19 AM
Hi vcoolio,

It's working as per my expectation.

Thanks a lot and keep rocking.

Regards,
Raj
0