Merging Data From 2 Workbooks

[slimano]

Hi Andrew,

I have 2 sets of data (100 and 101) for a set of entities (A, B) that i want to merge.
I have written a macro that combines all the data of 101 for A,B into one file (data 101) and also combines all the data of 100 for A,B into another file (data 100).

Now i want to merge the 2 files into a single master file called "combined" in a specific format and save it. Can you please help?

my code so far is as follows:

Sub tiso()
Dim fn, e, wb As Workbook
Dim LastRow As Long
Dim NextCell As Range
Dim First As Boolean
Dim sAppPath As String, sFileName As String, sDate As String

fn = Application.GetOpenFilename(FileFilter:="Microsoft excel files (*.xls), *.xls", _
                     Title:="Go to Data 100 and Press CTRL A to Select All the Files", MultiSelect:=True)
If Not IsArray(fn) Then Exit Sub
Set wb = Workbooks.Add
'wb.SaveAs Filename:="C:\tiso\compiled & Format(Date, "").xls"

sAppPath = "c:\tiso\"
sDate = Replace(FormatDateTime(Now(), vbShortDate), "/", "-")
sFileName = sAppPath & "Data 100  " & sDate & ".xls"

ActiveWorkbook.SaveAs Filename:= _
sFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

For Each e In fn
    With Workbooks.Open(e)
        With .Sheets(1)
            'LastRow = Range("A65536").End(xlUp).Row
            'Range("B15", Cells(LastRow + 1, "J")).Copy
            Range("a4:b4").Cells.Copy
            wb.Sheets(1).Cells(wb.Sheets(1).Cells.SpecialCells(11).Row + 2, 2).PasteSpecial xlPasteValues
            Range("b15 : h15,b17 : h17").Cells.Copy
            wb.Sheets(1).Cells(wb.Sheets(1).Cells.SpecialCells(11).Row + 2, 2).PasteSpecial xlPasteValues
           ' Range("b17 : h17").Cells.Copy
            'wb.Sheets(1).Cells(wb.Sheets(1).Cells.SpecialCells(11).Row + 1, 2).PasteSpecial xlPasteValues
        End With
        .Close False
    End With
Next

Application.CutCopyMode = False
Set wb = Nothing

fn = Application.GetOpenFilename(FileFilter:="Microsoft excel files (*.xls), *.xls", _
                     Title:="Go to Data 101 and Press CTRL A to Select All the Files", MultiSelect:=True)
If Not IsArray(fn) Then Exit Sub
Set wb = Workbooks.Add
'wb.SaveAs Filename:="C:\tiso\compiled & Format(Date, "").xls"

sAppPath = "c:\tiso\"
sDate = Replace(FormatDateTime(Now(), vbShortDate), "/", "-")
sFileName = sAppPath & "Data 101  " & sDate & ".xls"

ActiveWorkbook.SaveAs Filename:= _
sFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

For Each e In fn
    With Workbooks.Open(e)
        With .Sheets(1)
            'LastRow = Range("A65536").End(xlUp).Row
            'Range("B15", Cells(LastRow + 1, "J")).Copy
            Range("a4:b4").Cells.Copy
            wb.Sheets(1).Cells(wb.Sheets(1).Cells.SpecialCells(11).Row + 2, 2).PasteSpecial xlPasteValues
            Range("b15 : d16").Cells.Copy
            wb.Sheets(1).Cells(wb.Sheets(1).Cells.SpecialCells(11).Row + 2, 2).PasteSpecial xlPasteValues
           ' Range("b17 : h17").Cells.Copy
            'wb.Sheets(1).Cells(wb.Sheets(1).Cells.SpecialCells(11).Row + 1, 2).PasteSpecial xlPasteValues
        End With
        .Close False
    End With
Next

Application.CutCopyMode = False
Set wb = Nothing

End Sub

Data 100

Excel Workbook
	B	C	D	E	F	G	H
3	Data A	*	*	*	*	*	*
4	*	*	*	*	*	*	*
5	EQUIVALENT( MILLONS)	3292668	LONG	-11638	SHORT	737897.1	LONG
6	NET OWN FUNDS(NOFs)	7.32	LONG	-0.03	SHORT	1.64	LONG
7	*	*	*	*	*	*	*
8	Data B	*	*	*	*	*	*
9	*	*	*	*	*	*	*
10	EQUIVALENT( MILLONS)	3292668	LONG	-11638	SHORT	737897.1	LONG
11	NET OWN FUNDS(NOFs)	7.32	LONG	-0.03	SHORT	1.64	LONG
Sheet1

Data 101

Excel Workbook
	B	C	D
3	Data A	*	*
4	*	*	*
5	NOP IN PERCENTAGE%	27232600	LONG
6	AFOP IN PERCENTAGE	1.53	LONG
7	*	*	*
8	Data B	*	*
9	*	*	*
10	NOP IN PERCENTAGE%	27232600	LONG
11	AFOP IN PERCENTAGE	1.53	LONG
Sheet1

Combined should look like this:

Excel Workbook
	B	C	D	E	F	G	H	I
2	Data A	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*
4	EQUIVALENT( MILLONS)	4,380,526.71	LONG	22,732.22	LONG	496,252.16	LONG	*
5	NET OWN FUNDS(NOFs)	*	*	*	*	*	*	45,001,589.54
6	NOP IN PERCENTAGE	9.73	LONG	0.05	LONG	1.10	LONG	*
7	AFOP IN PERCENTAGE	*	*	*	*	*	*	12.72
8	*	*	*	*	*	*	*	*
9	Data B	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*
11	EQUIVALENT( MILLONS)	-16,985,152.40	SHORT	-464,904.61	SHORT	96,455.07	LONG	*
12	NET OWN FUNDS(NOFs)	*	*	*	*	*	*	264,186,952.00
13	NOP IN PERCENTAGE%	-6.43	SHORT	-0.18	SHORT	0.04	LONG	*
14	AFOP IN PERCENTAGE	*	*	*	*	*	*	-6.61
Sheet1

Can anyone please assist? Thanks