Consolidating two WBs using Macro

[haleybusche]

I have two workbooks (WB), A and B. Each has as single worksheet, so I will refer to them as WB A and WB B as there is only one worksheet in each. Each has a dynamic number of rows, A as well as B. The contents of each column is of the same type.

What I need is a macro that will combine the information from WB A column 1 and WB B column 1 to be in row 1 and 2 of Column 1 in a separate combined WB. Continued for a set number of columns (9).

So if I have 3 rows in WB A and 5 rows in WB B I should have 8 rows in the combined WB. But the number of rows in WB A and B will change dynamically, and I will need the combined workbook to accommodate this.

Thanks.

 

[Domenic]

Actually, when the macro is run again, it should...

1) clear all cells from the destination sheet

2) copy the data from the first source workbook (original data, plus any new data)

3) copy the data from the second source workbook (original data, plus any new data)

Is this not the case?

 

[haleybusche]

It seems to work sporadically. When I just tried it again, it worked. Then I changed a few things in the separate WBs saved, and tried to run the macro to update the combined workbook and it did not update. The new row I added in one of the separate workbooks did not appear in the combined workbook.

 

[haleybusche]

After more testing it appears it doesn't add the last line. So if I add a row at the bottom of WB A or B it won't add it in the combined WB. If I add in the middle it will copy and paste correctly. It appears to be erasing and pasting, but it doesn't copy the new last line and paste it to the combined WB. Any ideas?

 

[Domenic]

In which cells is the new data being entered?

 

[Domenic]

When entering a new row of data, if no data is entered in Column A, the new row will not be copied. Therefore, try the following instead...

[font=Verdana]    [color=darkblue]With[/color] wksA
        [color=darkblue]With[/color] .UsedRange
            LastRow = .Rows.Count + .Rows(1).Row - 1
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        .Range("A2:I" & LastRow).Copy _
            Destination:=wksDest.Cells(wksDest.Rows.Count, "A").End(xlUp).Offset(1)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] wksB
        [color=darkblue]With[/color] .UsedRange
            LastRow = .Rows.Count + .Rows(1).Row - 1
        End [color=darkblue]With[/color]
        .Range("A3:I" & LastRow).Copy _
            Destination:=wksDest.Cells(wksDest.Rows.Count, "A").End(xlUp).Offset(1)
    End [color=darkblue]With[/color]
    [/font]