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

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

Code:

---

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

---

[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