Consolidate Data As Per Header

[Shereena Kareem]

Hi Friens,

I have 30 excel files in a folder. i need to consolidate these files based on its header. for example in first file the first name in column "C", but in second file the first name is in Column "E". I need this two first name fields in Column "A" of the Master sheet.I have a macro to consolidate all the excel file in a folder. But this macro does not consolidate excel files as per header. Any help will be appreciable.

Thanks
Shereena

 

[Deepak]

Can we have sample xls pls.

 

[Shereena Kareem]

Can we have sample xls pls.

Hi Deepak,

Thanks for your quick response. I dont have the permission to upload the sample xls. I will explain my problem. In first excel file a column heading "First Name" is in column "C", but in second excel file the same heading is in column "E". Like wise the heading column is varying in all the excel files.

Thanks
Shereena

 

[Marc L]

Hi !

Use MATCH worksheet function (example in this thread)
with IsNumeric VBA function
or Find method to check headers (just read its VBA inner help
and its example, beginner level) …

Otherwise, join two sample (dummy data) source workbooks and
a filled sample consolidation workbook accordingly :
it will help to see your need !
​

 

[chirayu]

You could use something like this

Sub SampleCode()

Dim MyCol, MyRow, MyRng As String

'Find the Header
Cells.Find("Name").Activate

'Header Column Name
MyCol = Split(Cells(ActiveCell.Row, ActiveCell.Column).Address, "$")(1)

'Header Row Number
MyRow = Split(Cells(ActiveCell.Row, ActiveCell.Column).Address, "$")(2)

'Define Range of Rows in the Header column
MyRng = Range(MyCol & (MyRow + 1) & ":" & MyCol & Cells(Rows.Count, MyCol).End(xlUp).Row).Address

'Copy that Range
Range(MyRng).Copy

End Sub

 

[Marc L]

Chirayu, TBTO rule, check this :​

MyCol = ActiveCell.Column

MyRow = ActiveCell.Row

Your code is like from basement to go upstairs to 6th floor
then call lift to come back to basement !

Cells(ActiveCell.Row, ActiveCell.Column).Address
is equal to ActiveCell.Address ‼
​

 

[chirayu]

@Marc L
I know. I just made it like that as I prefer using column names to column numbers. otherwise I guess I could just use Range(Cells for the same