Excel Question- Consolidation of two WBs

[Nauas]

Hi All,
Appreciate if you can please help me resolving following issue I am facing.
I have set of two work books with data
Work Book 1:

	HOI	CITIK	OISCO	MITEL	SOFT	YBRW	AMA
Fixed Assets	1	2	45	5	5	45	45
Current Assets	3	4	5	5	5	5	5
Long term Liabilities	4	5	1	5	5	1	1
Short term liabilities	5	5	5	5	5	5	5
Equity	5	5	5	5	5	5	5

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

Work Book2:

	YBRW	HOI	OISCO	ALVIRO	MITEL	SOFT	CITIK	AMA
Sales	45	1	45	10	5	5	2	45
COST	5	3	5	8	5	5	4	5
Margin	1	4	1	2	5	5	5	1
Admin Exp	5	5	5	1	5	5	5	5
Profit	5	5	5	1	5	5	5	5

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

Above data is for Balance Sheet and P&L of various companies. But column order is usually different (but both work books are having information related to same companies) making it difficult to just copy and pasting the information of same company under each other. So I have to manually first bring them in same order so that I can just copy paste and bring balance sheet and P&L in same order. There might be instances where one or two companies are not present in any set of data(i.e work book)

Is there any way to automate this to avoid one whole day work. For me due to so many business units take a lot of time to manually adjust them.

 

[mumps]

What are the names of the two workbooks including extension and what are the names of the two worksheets in each workbook? In which of the two workbooks do you want to combine the data?

 

[Nauas]

Hi Mumps,
You can give any name.
Lets say
Workbook1 and workbook2 and Sheet1 of each work book. Lets support I wan to copy it on Sheet2 of workbook1.
is there any resolve?

 

[Nauas]

Is there anyone who can help here?

 

[mumps]

Copy and paste this macro into a regular module in Workbook1.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim srcSH As Worksheet
    Set srcSH = Workbooks("Workbook2.xlsx").Sheets("Sheet1")
    Dim LastRow As Long
    LastRow = srcSH.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim header As Range
    Dim foundHeader As Range
    srcSH.Range("A2:A" & LastRow).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    For Each header In Sheets("Sheet1").Range(Cells(1, 2), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
        Set foundHeader = srcSH.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcSH.Activate
            Range(Cells(2, foundHeader.Column), Cells(LastRow, foundHeader.Column)).Copy Workbooks("Workbook1.xlsm").Sheets("Sheet1").Cells(Rows.Count, header.Column).End(xlUp).Offset(1, 0)
        End If
    Next header
    Application.ScreenUpdating = True
End Sub

Save the Workbook1 as an Excel macro-enabled workbook so that its extension changes to "xlsm". Make sure that Workbook2 is saved as an Excel workbook with an "xlsx" extension. Both the source worksheet and the destination worksheet are named "Sheet1". Make sure that both workbooks are open and that Sheet1 in Workbook1 is the active sheet and then run the macro.

 

[Nauas]

Hi mumps,
Thanks for this. Let me try this today and I will seek your help if required.

 

[Nauas]

Hi Mumps,
Thanks for the code. I tried the code. but it is only copying the data from column A and only first two from column B.
Any quick fix to this ?

 

[Nauas]

Hi mumps this is my data:
Workbook1. Sheet 1 ( Macro enabled)

	BU No	10000	10001	10002	10003	10004	10005	10006
ABC	ADB	BYT	ARP	BRP	UOI	WOP
Fiscal year/period	2016	2016	2016	2016	2016	2016	2016
GL Account	Fiscal year/period	Actual Balance	Actual Balance	Actual Balance	Actual Balance	Actual Balance	Actual Balance	Actual Balance
Sales	AC0001	(44,376,189)	(8,841,190)	(4,602,972)	-	(4,602,972)	-	(4,238,218)
Trading Sales	AC0002	(85,474,564)	(66,698,482)	(2,558,517)	-	(2,558,517)	-	(64,139,965)
Rental income	AC0003	(10,824)	(10,824)	-	-	-	-	(10,824)
Expenses	AC0004	616,210	(86,538)	-	-	-	-	(86,538)
Admin expenses	AC0005	225,231	225,231	(23,578)	-	(23,578)	-	248,809
Other Income	AC0006	(4,039)	(4,039)	-	-	-	-	(4,039)
Profit	AC0007	(129,024,175)	(75,415,842)	(7,185,067)	-	(7,185,067)	-	(68,230,775)

<colgroup><col><col><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

Data of Workbook2: Sheet 1 (regular excel file)

	BU No	10006	10001	10003	10004	10000	10002	10005
WOP	ADB	ARP	BRP	ABC	BYT	UOI
Fiscal year/period	2016	2016	2016	2016	2016	2016	2016
GL Account	Fiscal year/period	Actual Balance	Actual Balance	Actual Balance	Actual Balance	Actual Balance	Actual Balance	Actual Balance
Fixed assets	BS0001	1,000,000	2,000,000	500,000	700,000	900,000	90,000	1,000,000
Current Asset	BS0002	1,000,000	2,000,000	500,000	700,000	900,000	90,000	1,000,000
Long term liability	BS0003	(1,000,000)	(2,000,000)	(500,000)	(700,000)	(900,000)	(90,000)	(1,000,000)
Short term liability	BS0004	(500,000)	(1,000,000)	(250,000)	(350,000)	(450,000)	(45,000)	(500,000)
Equity	BS0005	500,000	1,000,000	250,000	350,000	450,000	45,000	500,000

<colgroup><col><col><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

When I am using above code. It is only copy first two column and data of column A and header of column B.
Whereas I want to copy whole the whole information from Sheet two and also to align each name under the similar name on WB1.
Appreciate if you can please help.

 

[mumps]

In your original post, the headers in both workbooks were identical. In your last post, they are similar but not identical. That is why the code isn't working properly for you. Are the three lines of the headers all in one cell? For example:
10001
BRP
2016
Are these three lines all in cell D1 of Workbook2?

 

[Nauas]

Hi Mumps,
I am only concerned about coping of information based on numeric numbers i.e10001 ( these are the different company codes) since system generated reports are always giving those companies in different order so i had to cut and paste manually to align and then pasting balance sheet values under p&l of each company. The order of company names in columns will always be different. Is there any way out automate this?