Copying the worksheets in to multiple files in the folder

[gunjan8882]

Dear all,

I am trying to write a macro to copy the open sheet into multiple worksheets inside the folder. However, I am not sure of exact syntax. Please help me to correct the syntax.

Private Sub CommandButton10_Click()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("D:\Work\\Macro1")
Windows("abc.xlsx").Activate
Sheets("fox").Select


For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path
Sheets("fox").Copy After:=Workbooks
ActiveWorkbook.Close SaveChanges:=True
End If
Next file
Set oFSO = Nothing
End Sub

 

[John_w]

See if you can adapt https://www.mrexcel.com/forum/excel-questions/1092041-copy-push-sheet-post5248967.html#post5248967. Just change the folder path and sheet name.