View Full Version : Replace a sheet in all the files in a folder
fatalcore
02-24-2012, 07:46 PM
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
02-25-2012, 01:03 AM
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".
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
Hope this helps
fatalcore
02-25-2012, 04:26 AM
Thanks georgiboy,
That's exactly what I want.
Godbless !
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.