Hi ,
we have data of 500 sheets which contains the same format twise in the same
sheet, the column heading are Date, Place,Called Number, Code, Minutes which
appears twice in each sheet but both data are different.

Date Place CalledNumber Code Minutes Date Place CalledNumber Code
Minutes
0909 EL 654P EL RIO E 1:55 CC 0909 XL 604P EL RI
X 2:01 CC
0908 MM 624E LL OPI M :18 CC 0907 YY 601P ZL RI
Y :01 CC

like this data will be there twice in a single sheet. So what I want is
I need the data in a separate sheet from all 500 sheets after adding both
the "Minutes" colums.

Thanks in advance.

I create two macros. Both macros create a new workbook. Copies each sheet
into a sperate sheet in the new workbook. the second macro copies all the
data into a single worksheet called master. Not sure fromyour description
which one you wanted. they are both very similar and it was easy to create
two macros then to get it wrong.

Sub copysheets1()

First = True
For Each sht In ThisWorkbook.Sheets
If First = True Then
Set newbk = Workbooks.Add(template:=xlWBATWorksheet)
Set newsht = newbk.Sheets(1)
First = False
Else
With newbk
Set newsht = .Sheets.Add(after:=.Sheets(Sheets.Count))
End With
End If

With sht
newsht.Name = .Name
.Columns("A:E").Copy _
Destination:=newsht.Columns("A:E")

'get first new row in new workbook
LastRow = newsht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'get the last row in the 2nd columns of data
'the original workbook
LastRow = .Range("F" & Rows.Count).End(xlUp).Row
'don't copy header row
.Range("F2:J" & LastRow).Copy _
Destination:=newsht.Range("A" & NewRow)
End With

Next sht
End Sub

Sub copysheets2()

Set newbk = Workbooks.Add(template:=xlWBATWorksheet)

Set newsht = newbk.Sheets(1)
newsht.Name = "Master"
For Each sht In ThisWorkbook.Sheets
With sht
If newsht.Range("A1") = "" Then
'copy header row
.Range("A1:E1").Copy _
Destination:=newsht.Range("A1")
End If

'get first new row in new workbook
LastRow = newsht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'get the last row in the 1st columns of data
'in the original workbook
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:E" & LastRow).Copy _
Destination:=newsht.Range("A" & NewRow)

'get first new row in new workbook
LastRow = newsht.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1

'get the last row in the 2nd columns of data
'in the original workbook
LastRow = .Range("F" & Rows.Count).End(xlUp).Row
'don't copy header row
.Range("F2:J" & LastRow).Copy _
Destination:=newsht.Range("A" & NewRow)
End With

Next sht
End Sub



"Need Macro::Consolidating 500 sheets in" wrote:

Hi ,
we have data of 500 sheets which contains the same format twise in the same
sheet, the column heading are Date, Place,Called Number, Code, Minutes which
appears twice in each sheet but both data are different.

Date Place CalledNumber Code Minutes Date Place CalledNumber Code
Minutes
0909 EL 654P EL RIO E 1:55 CC 0909 XL 604P EL RI
X 2:01 CC
0908 MM 624E LL OPI M :18 CC 0907 YY 601P ZL RI
Y :01 CC

like this data will be there twice in a single sheet. So what I want is
I need the data in a separate sheet from all 500 sheets after adding both
the "Minutes" colums.

Thanks in advance.