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]

I still need an answer to my question in Post# 9. If you are copying data based on numbers such as 10000, 10001, 10002, etc., what do you do with column B which has no number in the header? Will the header in column B always be:
BU No
ADB
2016

 

[Nauas]

Hi mumps the column a and b will remain constant and copied with other data once they are aligned. The objective is to copy data from wb2 under wb1 based alignment of companies as mentioned in above post. Column a and b will require only copy as it is whereas from column c onwards i need to first align the wb 2 based on the company names with wb1z

 

[mumps]

Try:

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("A3:B" & LastRow).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    For Each header In Sheets("Sheet1").Range(Cells(1, 3), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column))
        Set foundHeader = srcSH.Rows(1).Find(Split(header, Chr(10))(0), LookIn:=xlValues, lookat:=xlPart)
        If Not foundHeader Is Nothing Then
            srcSH.Activate
            Range(Cells(3, 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

 

[Nauas]

Hi Mumps,
I really appreciate your help. But it is still coping the first two column. In the above code I changed

srcSH.Range("A3:B" & LastRow).Copy Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

"B" to name of my last column ( which is "I"). It is indeed copying the whole data from workbook 2 however it is not aligning the data based on the headers of workbook.

As mentioned earlier. The order of columns is different. So basically code should first align and bring the column in same order and then paste the values under workbook 1 from workbook 2.

I am really indebted to you for your all help. But i believe I am very close to resolve this issue.

Please help.

Regards,
Nauman

 

[Nauas]

Although it is copying the information but not aligning the header based on column data.
And before posting the information it is giving a warning error

"Subscript out of range"

 

[mumps]

Click the following links to download you workbooks:
https://app.box.com/s/wgglxt1hdpjgjm5uwsz8mne3ea6wozgl
https://app.box.com/s/xthpefadz53x8c5rne1t4xvyrbmh8zmw
Open both files and run the macro in Workbook1. Everything seems to be working according to your request.

 

[Nauas]

Thanks a lot. Let me see. I can not explain how much you have helped me. I was waiting for your reply

 

[Nauas]

Thanks buddy. It is working on your file. Let me do the same for my file also. This is really helpful.
I am really indebted.

 

[mumps]

Glad to help.

 

[Nauas]

by the way your file is perfectly working but same code on my files is not working. I will find out this and update you. Thanks a lot