Replace a sheet in all the files in a folder

[fatalcore]

Hi,
I have more than 72 files in a folder.
I have master data for all these files in "Sheet 4" in a Master file called "MSP Data".
I want a code that when called will update all the file's "Sheet 6" with Master Files "Sheet 4 called MSP Data".
Thanks in advance.

[georgiboy]

This should do as you need, it is moving sheet "Main data" from the workbook that has this code in and placing it in all Excel workbooks in file "C:\Demo" deleting the old "Main data" sheet as it loops.

It will delete the current "Main data" sheet and place the new "Main data" sheet at location 6 in the destination workbook. The "Main data" worksheet will have to exist in the destination workbooks otherwise it will throw a "wobbly" (error).

The workbook with this code in will need to be in a different file location to the destination workbooks because it is scanning for any .xls file in "C:\Demo".

[VBA]Sub SpreadWings()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

MyPath = "C:\Demo" 'change to suit

strFilename = Dir(MyPath & "\*.xls", vbNormal)
If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbDst = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
wbDst.Sheets("Main data").Delete 'delete the current out of date sheet

Set wbSrc = ThisWorkbook
Set wsSrc = wbSrc.Worksheets("Main data") 'change to suit

wsSrc.Copy After:=wbDst.Worksheets(5)

wbDst.Close True

strFilename = Dir()

Loop


Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/VBA]

Hope this helps

[fatalcore]

Thanks georgiboy,
That's exactly what I want.
Godbless !