Link to home
Start Free TrialLog in
Avatar of gixxer1020
gixxer1020Flag for United States of America

asked on

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
Avatar of FamousMortimer
FamousMortimer
Flag of United States of America image

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
Avatar of Rob Henson
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
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
Avatar of gixxer1020

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of FamousMortimer
FamousMortimer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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