VBA Code->copying sheet from mutiple sheets excel into single excel with diff sheets

[sudhir]

Hi,

i am having 200 excel file in folder ..each file has many sheet...i need 1 sheet (that is one common tab from different excels )from all excels to copied in one excel in different sheets(1 excel sheet data should be copied 1 sheet and second excel data copied in second sheet and so on) not in same sheet. Need it urgent please respond . Thanks

[mana]

Option Explicit


Sub test()
Dim ws As Worksheet
Dim wb As Workbook
Dim shn As String
Dim p As String
Dim f As String

Set ws = Workbooks.Add(xlWBATWorksheet).Sheets(1)

shn = "target"
p = "C:\*****\*****\"

f = Dir(p & "*.xlsx")

Do While f <> ""
Set wb = Workbooks.Open(p & f)
On Error Resume Next
wb.Worksheets(shn).Copy ws
ActiveSheet.Name = wb.Name
On Error GoTo 0
wb.Close False
f = Dir()
Loop

Application.DisplayAlerts = False
Fws.Delete
Application.DisplayAlerts = True

End Sub

[sudhir]

[QUOTE=mana;380340][CODE]Option Explicit

Code throwing errors. So I am attaching two workbooks which i am working on..I need country service sheets from both workbooks to copied and pasted in new workbook in different sheet. In New work book sheet1 should have country service details first work book and in sheet 2 Country service details of 2work book..Thanks very much if you can help me in this