Update multiple worbooks from 1 workbook

daren.beaney · Oct 12th 2011

Hi,

I have a master workbook called "APR Compliance Master.xls" and in this workbook I have 27 sheets with a name such as CDG. I also have 27 workbooks in a folder location on our network for example called f:\Test update files.

What I've been asked to do is see whether it's possible to:

use an input box to define the new sheet name for each workbook (which will be the same name for all workbooks eg "Sept")
go through each sheet in my master file, copy the content in the currentregion and then...
go to the folder location above and find the file that contains the master workbook activesheet name such as CDG (the file also has additional text such as version)
create a new sheet at the end and use the name from step 1
paste in the data copied and save and close the workbook
go back to step 2

Hope that makes sense. I've been searching for weeks on this but can only find ways to merge content from multiple workbooks into a single sheet.

Kind regards,
Daren

Smallman · Oct 13th 2011

Re: Update multiple worbooks from 1 workbook

Hi Daren

OK so I set up a folder with 4 workbooks for demo purposes. CDG, ABC and Test and Random. My workbook had 3 sheets called CDG, ABC and Test. I fired the following code. It asks the user for a name, for the new sheet. It will copy the contents of the current region of the sheet name that matches the file name.

For example the current region of Sheet CDG will copy into the CDG Ver xx workbook and so on. It will not open or paste anything onto the Random workbook as there are no sheets with this name. Should work no matter how many sheets you have. Hope this helps.

Take care

Smallman

Code

Option Explicit
Sub OpenEx()
Dim sPath As String
Dim sFil As String
Dim strName As String
Dim oWbK As Workbook
Dim sh As Worksheet
Dim ws As Worksheet
Dim Temp As String
Dim shNm As String
Temp = InputBox("Enter Sheet Name")
sPath = "D:\Test\Add\" 'Change path to suit


For Each ws In ThisWorkbook.Worksheets
 shNm = ws.Name
  sFil = Dir(sPath & shNm & "*.xls")
    Do While sFil <> ""
        strName = sPath & sFil
        Set oWbK = Workbooks.Open(strName)
        sFil = Dir
        Set sh = Worksheets.Add
        sh.Name = Temp
        sh.Move After:=Sheets(Worksheets.Count) 'Pastes to end
        ws.Range("A1").CurrentRegion.Copy
        sh.Range("A1").PasteSpecial xlPasteValues 'Do you want the vals pasted?
        oWbK.Close True
     Loop
Next
End Sub

Display More

daren.beaney · Oct 13th 2011

Re: Update multiple worbooks from 1 workbook

Hi Smallman,Thanks for the reply!I tried the code and it seems to work fine to a point and reason being is my file names have other text i.e. 2011-2012 DPM CDG v12.xls it doesn't seem to find the file containing CDG. Is there anyway it can search for the file that contains the sheet name in the string?Really appreciate this!Daren

Smallman · Oct 13th 2011

Re: Update multiple worbooks from 1 workbook

Hi Daren

In your example you described the extra text being a version. So in my head you were saying CDG V2. Without testing it I would suggest you put the wildcard symbol before the shNm variable. Leave it in after as you probably have all manner of names in the mix.

Take care

Smallman

Code

sFil = Dir(sPath & "*" & shNm & "*.xls")

daren.beaney · Oct 13th 2011

Re: Update multiple worbooks from 1 workbook

Smallman,Thanks for the reply, it was down to my network path, for some reason it doesn't like the path. I tried it locally and it worked a treat!Thanks for your help on this!

daren.beaney · Nov 2nd 2011

Re: Update multiple worbooks from 1 workbook

Hi Smallman,I did say that this was working however I cant get it to work...I've checked the sheet names and file names, even created files with the exact sheet name.Can you see any issues in my code below?Thanks,Daren

Code

Dim sPath As String    Dim sFil As String    Dim strName As String    Dim oWbK As Workbook    Dim sh As Worksheet    Dim ws As Worksheet    Dim Temp As String    Dim shNm As String    Temp = InputBox("Enter Sheet Name")    sPath = "G:\Master Files for HRBP" 'Change path to suit        For Each ws In ThisWorkbook.Worksheets        shNm = ws.Name        sFil = Dir(sPath & "*" & shNm & "*.xls")                Do While sFil  ""            strName = sPath & sFil            Set oWbK = Workbooks.Open(strName)            sFil = Dir            Set sh = Worksheets.Add            sh.Name = Temp            sh.Move After:=Sheets(Worksheets.Count) 'Pastes to end            ws.Range("F1").CurrentRegion.Copy            sh.Range("A1").PasteSpecial xlPasteValues 'Do you want the vals pasted?            oWbK.Close True        Loop    Next

Update multiple worbooks from 1 workbook

Participate now!

Share