Dear Forum members,

Kindly help for the following requirements.

I have 8 macro enabled excel workbooks in a folder. such as India, China, Australia, USA, UK, Germany, France, Italy and each workbook has 3 worksheets. (A, B, C)

Requirement as follows:

1. I have a consolidation sheet called "Master", which has 3 worksheets (A, B, C) as same as excel workbooks, which have been kept in the folder.

2. Now, i want to copy "A" worksheets data from "India, China, Australia, USA, UK, Germany, France, Italy" and consolidate in "Master" consolidation workboook, "A" Worksheet. The data range starts from "A4".

3. Like wise, i want to copy "B" and "C" data from "India, China, Australia, USA, UK, Germany, France, Italy" and and consolidate in "Master" consolidation workboook, "B" and "C" respective Worksheet. The data range starts from "A4".


I have tried with the following code. But I have some issues and other requirements as follows:

1. Is there any possibilities, instead of giving file path in the following coding, automatically window should be opened and select required files to consolidate.
2. Actually code works well, but when consolidate the excel worksheets "date format" is changed from DD/MM/YYYY to MM/DD/YYYY.

Kindly do the needful.

CODE]Sub btnmerge_Click()
Dim erow As Long, lastrow As Long, lastcolumn As Long
MsgBox "Kindly ensure the existing data was cleared or not"

For counter = 1 To 3
Workbooks.Open Filename:="C:\Users\dell\Downloads\PUDR_Oct_18_Mar_19\TA_Tool_Consolidation_Downloaded_Tool\1. India.xlsx"
'Sheets("Sheet1").Select
Worksheets(counter).Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 1), Cells(lastrow, lastcolumn)).Copy
ActiveWorkbook.Close
'Sheets("Sheet1").Select
Worksheets(counter).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste
Next

For counter = 1 To 3
Workbooks.Open Filename:="C:\Users\dell\Downloads\PUDR_Oct_18_Mar_19\TA_Tool_Consolidation_Downloaded_Tool\2. China.xlsx"
'Sheets("Sheet1").Select
Worksheets(counter).Select
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 1), Cells(lastrow, lastcolumn)).Copy
ActiveWorkbook.Close
'Sheets("Sheet1").Select
Worksheets(counter).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
ActiveSheet.Paste
Next
[/CODE]


Bala