Your description of the requirement is pretty vague but I have put
together a scenario to give you an idea of an approach to a solution.

1. How to write a macro which will open up a dialogue box and asked user
to select a particular excel file.

-Let's start with the simple Method

selectFile = InputBox("Enter one of the following file names:" & Chr(13) &
"FileName1" & Chr(13) & "FileName2", "File Name")
'The Chr(13) inserts a carriage return so the files will be on separate lines.

2. Now selecting a excel workbook which has multiple worksheets. such as
A1, A2, A3 ...etc.

-This should be the result of the selection in 1 above

Workbooks.Open Filename:=selectFile

3. Each worksheet has a column name(e.g, class name, parent name..).

- This is normally developed by the creator of the workbook.

4. It should copy the content of each worksheet to another file based on
column name

checking(i,e,if the column name is same) some value.

- Assuming the range is named myRange

If myRange cells(1, 1) < "" Then
Worksheets(1).Cells.Copy Workbooks(2).Worksheets(1).Range("A1")
End If

" wrote:

How to write a macro which will open up a dialogue box and asked user
to select a particular excel file.Now selecting a excel workbook which
has multiple worksheets. such as A1, A2, A3 ...etc. Each worksheet has
a column name(e.g, class name, parent name..). It should copy the
content of each worksheet to another file based on column name
checking(i,e,if the column name is same) some value.