VBA code for multiple xls files

[bruggied]

i want that a vba code can be repeated to multiple xls files
what is the meaning of it, it needs to copy a sheet from the original file C95_TEAM.XLS to 15 other xls files
I have this vba code to copy the sheet but I want to repeat this to the other files
I used a vba code to look into a folder but also includes the file C95_TEAM.XLS
So I don’t a way to run this code to several xls files in the same folder
this is the code :

sheetname = InputBox("Vul de sheetname in.")
Windows("C95_TEAM.XLS").Activate
ChDir "C:\noliko-msk-2014-2015-vb"
Workbooks.Open Filename:="C:\noliko-msk-2014-2015-vb\C95_3_3.XLS"
Windows("C95_TEAM.XLS").Activate
Sheets(sheetname).Select
Sheets(sheetname).Copy After:=Workbooks("C95_3_3.XLS").Sheets(1)
Windows("C95_3_3.XLS").Activate
Sheets("3_3").Select
Range("A1").Select
Workbooks("C95_3_3.XLS").Close savechanges:=True

 

[John_w]

Try this. Put the code in a module in the C95_TEAM.XLS workbook.

Public Sub Copy_Sheet_To_Workbooks()

    Dim sheetName As String
    Dim fileName As String
    Dim wb As Workbook
    
    sheetName = InputBox("Name of sheet to be copied")
    If sheetName = "" Then Exit Sub
    
    fileName = Dir(ThisWorkbook.Path & "\*.xls")
    While fileName <> ""
        If fileName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & fileName)
            ThisWorkbook.Sheets(sheetName).Copy After:=wb.Sheets(1)
            wb.Close True
        End If
        fileName = Dir()
    Wend

End Sub

 

[bruggied]

Try this. Put the code in a module in the C95_TEAM.XLS workbook.

Public Sub Copy_Sheet_To_Workbooks()

    Dim sheetName As String
    Dim fileName As String
    Dim wb As Workbook
    
    sheetName = InputBox("Name of sheet to be copied")
    If sheetName = "" Then Exit Sub
    
    fileName = Dir(ThisWorkbook.Path & "\*.xls")
    While fileName <> ""
        If fileName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & fileName)
            ThisWorkbook.Sheets(sheetName).Copy After:=wb.Sheets(1)
            wb.Close True
        End If
        fileName = Dir()
    Wend

End Sub

tnx John i'll try this out and let you know it it's worked