Move only completed data from one table to another

I have a table with that is linked to another table.
Only the rows that have data in ALL the cells should show in the linked table.

The attached file has an example of the results I'm looking for but there are no formulas in it.

Thank you in Advance
Test-Sheet-01.xls
gixxer1020Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Would a button work for you?  Or do you need a formula?

See the attached workbook.  I used vba to create a function that does what you are asking.

It might have to be adjusted depending on where your tables are in the real workbook.

Here is the code used:
Sub CopyData()
    Dim r As Long
    Dim cnt As Long
    
    cnt = 6
    
    For r = 6 To Range("B65536").End(xlUp).Row
        If Application.WorksheetFunction.CountA(Range(Cells(r, 2), Cells(r, 5))) = 4 Then
            Range(Cells(r, 2), Cells(r, 5)).Copy Destination:=Cells(cnt, 8)
            cnt = cnt + 1
        End If
    Next r
End Sub

Open in new window

Test-Sheet-01.xls
0
Rob HensonFinance AnalystCommented:
Can you set a flag in the table that indicates whether the data for that row is complete?

If so you can have a routine that will use the Advanced Filter function to copy completed data to another location for onward copying to your subsequent table and then delete from the original.

Thanks
Rob H
0
FamousMortimerCommented:
This Application.WorksheetFunction.CountA(Range(Cells(r, 2), Cells(r, 5))) = 4 is like a flag that the row is complete in this example.

Try this one... (I realized that the button did not call the function in the last one.)

Sub CopyTable()
    Application.ScreenUpdating = False
    Dim tbl1 As Range
    Set tbl1 = Range("B5:E12")
    With tbl1
        .AutoFilter Field:=1, Criteria1:="<>"
        .Copy Destination:=Range("H5")
        .AutoFilter
    End With
    ClearComplete tbl1
    Application.ScreenUpdating = True
End Sub

Sub ClearComplete(tbl As Range)
    Dim r As Long
    
    For r = 6 To 5 + tbl.Rows.Count - 1
        If Application.WorksheetFunction.CountA(Range(Cells(r, 2), Cells(r, 5))) = 4 Then Range(Cells(r, 2), Cells(r, 5)).ClearContents
    Next r
    
    tbl.Sort Key1:=Range("C5"), Header:=xlYes
End Sub

Open in new window

Test-Sheet-01.xls
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

gixxer1020Author Commented:
Hi Famous,

You're on the right track, but I definitely do not want to delete the information from the original table.
Also, I need to have the code run when I leave the worksheet on the Worksheet_Deactivate command.

Thanks,

Edwin
0
FamousMortimerCommented:
My fault, I thought from the previous post that you wanted to remove it.

Here is the code I have added to the Sheet object and the file attached.  You will probably have to adjust the ranges to suit your real workbook.  Let me know if I can help.

Option Explicit


Private Sub Worksheet_Deactivate()
    Application.ScreenUpdating = False
    Dim tbl1 As Range
    Set tbl1 = Range("B5:E12")
    With tbl1
        .AutoFilter Field:=1, Criteria1:="<>"
        .Copy Destination:=Range("H5")
        .AutoFilter
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

-FM
Test-Sheet-01.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
If not removing data then I think the Advanced Filter function copying to another location would be ideal. The destination table would gave to be cleared otherwise you end up with duplicates in that table.

thanks
Rob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.