Match the HEADINGS

[Ranjit kurian]

Hi

Everyday i recv many files(workbookS) from clients, i need to paste each
excel files to our master file.

So, whenever i open any of the client file(workbook2) and master file
(workbook1) the macro should match the heading and copy the data from
client file , i do't need macro to open the file because the file name always
differs, but i require a general macro code for HEADINGS, which ever files i
open if the active workbook1 Headings is matching to the active workbook2
Headings then the macro should copy the data to the respective heading.

HEADINGS count are not fixed, but the heading are always at
ROW1(exampleA1:BZ1) both for client file and master file.

 

[Joel]

See if this code helps/

Sub GetData()

filetoopen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoopen = False Then
MsgBox ("Cannot open file - Exiting Macro")
Exit Sub
End If

Set Oldbk = Workbooks.Open(Filename:=filetoopen)
Set OldSht = Oldbk.Sheets("sheet1")
Set NewSht = ThisWorkbook.Sheets("sheet1")

With ThisWorkbook.Sheets("sheet1")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

With OldSht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
LastCol = .Range(1, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
NewRowCount = NewRow
If ColCount = 1 Then
'copy header Rows from col A
For OldRowCount = 2 To LastRow
NewSht.Cells(NewRowCount, "A") = _
OldSht.Cells(OldRowCount, "A")
NewRowCount = NewRowCount + 1
Next OldRowCount
Else
'Match heading columns
Header = .Cells(1, ColCount)
Set c = NewSht.Rows(1).Find(what:=Header, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could Not find Header : " & Header)
Else
For OldRowCount = 2 To LastRow
NewSht.Cells(NewRowCount, c.Column) = _
OldSht.Cells(OldRowCount, ColCount)
NewRowCount = NewRowCount + 1
Next OldRowCount
End If
End If
Next ColCount
End With

End Sub

 

[Ranjit kurian]

Hi Joel,

In macro the popup is asking to open file, which file should i open clients
files (datas are available but the heading are not arranged as master file)
or my master file(where the data need to copied as per heading)

and the macro is throwing debug in code "LastCol = .Range(1,
Columns.Count).End(xlToLeft).Column"

example:
Master file: below given is the heading

Mon Tue Wed Thu Fri Sat Sun

Client File: below given is the heading and data

Sat Sun Wed Thu Mon Fri Tue
6 7 3 4 1 5 2

The macro need to update the master file as shown below:

Mon Tue Wed Thu Fri Sat Sun
1 2 3 4 5 6 7


Note: Sheet Name, File Name, Headings are not same every day, keep changes,
means i need to run the same macro to all my client files.

 

[Joel]

I fixed the line below. The code was meant to go in the Master Workbook and
to select the Clients file. Does that make sense?


from
LastCol = .Range(1, Columns.Count).End(xlToLeft).Column
to
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column

 

[Ranjit kurian]

Hi Joel

The code is not throwing any debug, but it says the headings are not found.

First i opened my Client workbook, then used macro code, when i was running
macro it asked to open file, at that time i opend my master data from macro
code
Did you use 'activewindow next ' in your code, because i do't think the
macro is comparing the both client and master workbooks

 

[Joel]

do the headers in each sheet match EXACTLY? Are the headre names in the
Messaage box correct? Make sure there arre no spaces at the beginning and
end of the Header Names. The code is looking for the Headers on Sheet1 of
the master workbook

ThisWorkbook.Sheets("sheet1")


The sheet name in this l.ine may need to be changed.

 

[Ranjit kurian]

Thanks a lot......

Its working fine, actually i pasted the macro codes to a new workbook and
tried to run the code that was the mistake i did.