VBA Create a table from two different workbooks based on Active status and report missing data or errors

[Nena99]

I want to merge two tables from two different workbooks and that is what I managed to do so far.
The code I have is good to look for the rows that have active in them but it keeps empty rows in between the new table. Meaning, ID 1 and 4 are active but there is two rows non active and unknown so it wont copy them and I will have similar to the picture
Could someone help me to add a line so it does not leave an empty rows?

**Also I would like to add a line so it look for the ID in another table and copy the row and bring it to the new table.

**There are errors in the line (4 Stevens) so how can I make it report it back to me that there is error?

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
   ' loop column H untill last cell with value (not entire column)
   For Each Cell In .Range("G1:G" & .Cells(.Rows.Count, "H").End(xlUp).Row)
       If Cell.Value = "Active" Then
            ' Copy>>Paste in 1-line (no need to use Select)
           .Rows(Cell.Row).Copy Destination:=Sheets(4).Rows(Cell.Row)
           
       End If
   Next Cell
End With

End Sub

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[DacEasy]

Hi, post some actually data and I will look at it. Or if you find all my post, I may have done something similar that you can build your code from. Good luck.

 

[Nena99]

Workbook 1 has:

ID Name DOB STATUS
1 Johns 15/12/1972 Active
2 Alvis 06/04/1973 Inactive
3 Paul 27/08/1977 Unknow
4 Stevens 28/08/1980 Active

Workbook 2 has:
ID NO Name Totalleft
1 AA01 Johns
2 AA02 Alvis
3 AA03 Paul
4 #N/A Stevens 0.5424


I managed to do the first part, were it find the active cells. However, when it paste the rows it will look like

ID Name DOB STATUS
1 Johns 15/12/1972 Active


4 Stevens 28/08/1980 Active

Instead of

ID Name DOB STATUS
1 Johns 15/12/1972 Active
4 Stevens 28/08/1980 Active

I managed to remove the empty rows but it is a seperate macro, not within the same code using

Sub DeleteBlankRows()
On Error Resume Next
Range("A3:A1000000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

 

[Nena99]

The new merged table should look like

1 Johns 15/12/1972 Active
4 Stevens 28/08/1980 Active 0.5424

and because there is an empty cell, the macro should be able to report it
Could you please help me?

 

[Nena99]

The new merged table should look like

1 Johns 15/12/1972 Active
4 Stevens 28/08/1980 Active 0.5424

and because there is an empty cell, the macro should be able to report it
Could you please help me?

As I just want the total left from the second workbook, and the total of it?

 

[mumps]

Please follow the instructions in Post #2.

 

[DacEasy]

Book1
	A	B	C	D
1	ID	Name	DOB	STATUS
2	1	Johns	15/12/1972	Active
3	2	Alvis	6/4/1973	Inactive
4	3	Paul	27/08/1977	Unknow
5	4	Stevens	28/08/1980	Active
6
7
8
9	ID	NO	Name	Totalleft
10	1	AA01	Johns
11	2	AA02	Alvis
12	3	AA03	Paul
13	4	#N/A	Stevens	0.5424
Sheet1

Cool, I see how it works now.

 

[mumps]

Unfortunately, that didn't work out well. Select all the data in the sheet and then in the XL2BB add-in select "mini sheet".

 

[Nena99]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).