Hi Mark

I could use your code in subroutine, only thing is added following addtional line in your codeOnly thing i did not use Option Explicit anywhere...I dont know how to use it

[VBA]Set ThisWb = Workbooks("Consolidated.xls")
[/VBA]

I also added additional subrouitne to delete few columns from all the sheets in Consolidated.xls.

Here is my new code which worked fine

[VBA]Sub Copy_Data()

Dim ThisWb As Workbook
Dim wb As Workbook

Set ThisWb = Workbooks("Consolidated.xls")

'to reset workbook
Call Resetting

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/Euro.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ActiveSheet.Name = wb.Name
'ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/USD.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ActiveSheet.Name = wb.Name
'ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/JPY.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ActiveSheet.Name = wb.Name
'ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

Set wb = Workbooks.Open(FileName:=("C:/VBA Source/GBP.xls"))
wb.Sheets(1).Copy After:=ThisWb.Sheets(1)
ActiveSheet.Name = wb.Name
'ThisWb.Worksheets(2).Name = Replace(Replace(wb.Name, ".xls", ""), "C:/VBA Source/", "")
wb.Close

'For Deleting Colums
Call Deletecolumn

MsgBox ("Copying worksheets completed")

End Sub
Sub Resetting()
Dim wks As Worksheet
Set ThisWb = Workbooks("Consolidated.xls")

For Each wks In ThisWb.Worksheets
If Not wks.Name = "Sheet1" Then
Application.DisplayAlerts = False
wks.Delete
Application.DisplayAlerts = True
End If
Next

End Sub

Sub Deletecolumn()

ActiveWorkbook.Sheets.Select
Columns("B:AG").Select
Selection.Delete Shift:=xlToLeft
Columns("C:BF").Select
Selection.Delete Shift:=xlToLeft
'ActiveCell.FormulaR1C1 = "RECEIVE_CCY"
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("G:AC").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

End Sub
[/VBA]

Do suggest changes if any