Macro help required for getting data from multiple excel files.

[saranyas366]

Dear All,

I have 1000s of same structure excel files in a folder.

I want to extract some common details to one single excel file.

Ex. in excel file book1.xls, page a1 relates to name of customer 1, a2 relates to his address, a3 relates his contact number. etc..
As well as in book 2.xls, page a1 relates to name of customer 2, a2 relates to his address, a3 relates his contact number. etc..

I want to extract all these particulars to one single excel page.

I need some expert advice from all of you

Thanks in advance.

Ms. Saranya, Kerala, India

 

[spremkishan]

Please share both the files..
A VBA Macro could solve the issue.

Regards
Prem

 

[sai85]

Dear All,

I have 1000s of same structure excel files in a folder.

I want to extract some common details to one single excel file.

Ex. in excel file book1.xls, page a1 relates to name of customer 1, a2 relates to his address, a3 relates his contact number. etc..
As well as in book 2.xls, page a1 relates to name of customer 2, a2 relates to his address, a3 relates his contact number. etc..

I want to extract all these particulars to one single excel page.

I need some expert advice from all of you

Thanks in advance.

Ms. Saranya, Kerala, India

Hi Saranya,

I guess below code will help you to resolve your issue.

Let me know for any further clarification.

Sub Consolidation()

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Reports.xls"
ThisWorkbook.Activate
Range("C6").Select
Do While ActiveCell.Value <> ""


Workbooks.Open Filename:=ActiveCell.Value
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Workbooks("Reports").Activate
ActiveCell.PasteSpecial xlPasteAll
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ThisWorkbook.Activate
ActiveCell.Offset(1, 0).Select
Loop




End Sub

Kindly Enter the path of source files in MacroFile

For Example:

C:\Users\Desktop\Sai\Test1

<tbody>
</tbody>

 

[saranyas366]

Name	Address 1	Address 2	Address 3	Address 4	Address 5	Phone Number
Pinson	PADASSERIL HOUSE	KAPLIPPARA	MUNDUR POST	Palakkad	16 kerala	9447943121
RAMAKRISHNAN M	SREENIVAS'	KALLEKKAD	NEAR AR CAMP	Palakkad	16-Kerala	9497963119
P A ABDUL GAFOOR	ASHIK MANZIL	KANAYAD	VADAVANNOOR PO	PALAKKAD	16-KERALA	9995280130

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

 

[saranyas366]

Dear Sir,

I tried in VBE...Reports.xls created but one error occured while pressing Run button.

Can you explain with screenshots?

Thanks in advance.