gixxer1020
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
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
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
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.WorksheetFunct ion.CountA (Range(Cel ls(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.)
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
Test-Sheet-01.xls
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
thanks
Rob
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:
Open in new window
Test-Sheet-01.xls