Updating Multiple Workbooks

I have a file which serves as a master template. Over time, I have used this
for several workbooks which contain financial data on companies, and
therefore now have over 100 different workbooks based off this template. The
problem now is that the master template had to be updated, and I need the
changes to be reflected in each of the workbooks saved under the old
template.

The updates that need to be made are fairly simple, and mostly include new
values or formulas in cells. What I would like to do is have the master
template workbook open, and then have some code that will go through all
workbooks in a directory one by one, open each workbook, and copy certain
certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
for this example) from the master template to the old file with the outdated
template.

Is this difficult to do. I would appreciate any code that could be provided
to accomplish this. Thanks.

Hi Jason

Start here
http://www.rondebruin.nl/copy4.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jason" <[email protected]> wrote in message news:[email protected]...
>I have a file which serves as a master template. Over time, I have used this
> for several workbooks which contain financial data on companies, and
> therefore now have over 100 different workbooks based off this template. The
> problem now is that the master template had to be updated, and I need the
> changes to be reflected in each of the workbooks saved under the old
> template.
>
> The updates that need to be made are fairly simple, and mostly include new
> values or formulas in cells. What I would like to do is have the master
> template workbook open, and then have some code that will go through all
> workbooks in a directory one by one, open each workbook, and copy certain
> certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
> for this example) from the master template to the old file with the outdated
> template.
>
> Is this difficult to do. I would appreciate any code that could be provided
> to accomplish this. Thanks.

Thanks Ron, this is very helpful. A follow up question... Is it possible to
also transfer VBA modules as well. For example, assume I have a module which
contains the old code, but I have since updated that code in the same module,
can I use code to delete the old module and transfer the new module to the
old workbook. If so, what would the code look like? Also, I assume if I
have code in a worksheet and I use the code you provide to move the
worksheet, the associated code will also get transferred with it. Is that
correct? Thanks.

"Ron de Bruin" wrote:

> Hi Jason
>
> Start here
> http://www.rondebruin.nl/copy4.html
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Jason" <[email protected]> wrote in message news:[email protected]...
> >I have a file which serves as a master template. Over time, I have used this
> > for several workbooks which contain financial data on companies, and
> > therefore now have over 100 different workbooks based off this template. The
> > problem now is that the master template had to be updated, and I need the
> > changes to be reflected in each of the workbooks saved under the old
> > template.
> >
> > The updates that need to be made are fairly simple, and mostly include new
> > values or formulas in cells. What I would like to do is have the master
> > template workbook open, and then have some code that will go through all
> > workbooks in a directory one by one, open each workbook, and copy certain
> > certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
> > for this example) from the master template to the old file with the outdated
> > template.
> >
> > Is this difficult to do. I would appreciate any code that could be provided
> > to accomplish this. Thanks.
>
>
>

Thanks Ron, this is very helpful. A follow up question... is it also
possible to use code to transfer modules which have been updated as well?
Also, if I were to use the code which you provided to copy sheets, would the
VBA code that is stored directly in that sheet also be transferred? Thanks

Jason

"Ron de Bruin" wrote:

> Hi Jason
>
> Start here
> http://www.rondebruin.nl/copy4.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Jason" <[email protected]> wrote in message news:[email protected]...
> >I have a file which serves as a master template. Over time, I have used this
> > for several workbooks which contain financial data on companies, and
> > therefore now have over 100 different workbooks based off this template. The
> > problem now is that the master template had to be updated, and I need the
> > changes to be reflected in each of the workbooks saved under the old
> > template.
> >
> > The updates that need to be made are fairly simple, and mostly include new
> > values or formulas in cells. What I would like to do is have the master
> > template workbook open, and then have some code that will go through all
> > workbooks in a directory one by one, open each workbook, and copy certain
> > certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
> > for this example) from the master template to the old file with the outdated
> > template.
> >
> > Is this difficult to do. I would appreciate any code that could be provided
> > to accomplish this. Thanks.
>
>
>

Hi Jason

If you use the sheet option it will copy the sheet code also.
For changing the module code look at Chip Pearson's site
http://www.cpearson.com/excel/vbe.htm

If you need more help post back


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jason" <[email protected]> wrote in message news:[email protected]...
> Thanks Ron, this is very helpful. A follow up question... Is it possible to
> also transfer VBA modules as well. For example, assume I have a module which
> contains the old code, but I have since updated that code in the same module,
> can I use code to delete the old module and transfer the new module to the
> old workbook. If so, what would the code look like? Also, I assume if I
> have code in a worksheet and I use the code you provide to move the
> worksheet, the associated code will also get transferred with it. Is that
> correct? Thanks.
>
> "Ron de Bruin" wrote:
>
>> Hi Jason
>>
>> Start here
>> http://www.rondebruin.nl/copy4.html
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "Jason" <[email protected]> wrote in message news:[email protected]...
>> >I have a file which serves as a master template. Over time, I have used this
>> > for several workbooks which contain financial data on companies, and
>> > therefore now have over 100 different workbooks based off this template. The
>> > problem now is that the master template had to be updated, and I need the
>> > changes to be reflected in each of the workbooks saved under the old
>> > template.
>> >
>> > The updates that need to be made are fairly simple, and mostly include new
>> > values or formulas in cells. What I would like to do is have the master
>> > template workbook open, and then have some code that will go through all
>> > workbooks in a directory one by one, open each workbook, and copy certain
>> > certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
>> > for this example) from the master template to the old file with the outdated
>> > template.
>> >
>> > Is this difficult to do. I would appreciate any code that could be provided
>> > to accomplish this. Thanks.
>>
>>
>>

Ron,

Your code works beautifully, however, I need to twist it around a little
bit, and when I do, I get a run time error. What I want to do is instead of
having the base file go through an entire directory and update from the base
file to all other files, I want to have the base file open to start, have the
code run so that it opens only one workbook (I'll probably just leave one
workbook in the directory at a time for simplification), and copy from the
newly opened file to the base file. So in essence, I'm doing something
similar but have just switched around the copying procedures. When I do
this, I get the run time error "Run Time Error '1004': Select method of
range class failed". The code runs until it hits the line "Set
sourceDescription = mybook...". Any idea of how to get around this, code is
posted below. Thanks.

Jason

Sub Transfer()

Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceDescription As Range
Dim destDescription As Range

Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "P:\PRT\CREDITS\CreditReview"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = True
Set basebook = ThisWorkbook
Do While FNames <> ""
Application.EnableEvents = False
Set mybook = Workbooks.Open(FNames)
Application.EnableEvents = True

Set sourceDescription = mybook.Worksheets("TearSheet").Range("K20")
Set destDescription = basebook.Worksheets("TearSheet").Range("K20")
sourceDescription.Copy destDescription

basebook.Save
basebook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

"Ron de Bruin" wrote:

> Hi Jason
>
> If you use the sheet option it will copy the sheet code also.
> For changing the module code look at Chip Pearson's site
> http://www.cpearson.com/excel/vbe.htm
>
> If you need more help post back
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Jason" <[email protected]> wrote in message news:[email protected]...
> > Thanks Ron, this is very helpful. A follow up question... Is it possible to
> > also transfer VBA modules as well. For example, assume I have a module which
> > contains the old code, but I have since updated that code in the same module,
> > can I use code to delete the old module and transfer the new module to the
> > old workbook. If so, what would the code look like? Also, I assume if I
> > have code in a worksheet and I use the code you provide to move the
> > worksheet, the associated code will also get transferred with it. Is that
> > correct? Thanks.
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Jason
> >>
> >> Start here
> >> http://www.rondebruin.nl/copy4.html
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >>
> >> "Jason" <[email protected]> wrote in message news:[email protected]...
> >> >I have a file which serves as a master template. Over time, I have used this
> >> > for several workbooks which contain financial data on companies, and
> >> > therefore now have over 100 different workbooks based off this template. The
> >> > problem now is that the master template had to be updated, and I need the
> >> > changes to be reflected in each of the workbooks saved under the old
> >> > template.
> >> >
> >> > The updates that need to be made are fairly simple, and mostly include new
> >> > values or formulas in cells. What I would like to do is have the master
> >> > template workbook open, and then have some code that will go through all
> >> > workbooks in a directory one by one, open each workbook, and copy certain
> >> > certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
> >> > for this example) from the master template to the old file with the outdated
> >> > template.
> >> >
> >> > Is this difficult to do. I would appreciate any code that could be provided
> >> > to accomplish this. Thanks.
> >>
> >>
> >>
>
>
>

You sure it's "select method of range class failed"? You don't mean to be
selecting anything in code.

I was gonna guess that it was because "TearSheet" didn't exist in that workbook.

But you would have gotten a "run time error 9--subscript out of range".

If it was a typo in the newsgroup post, maybe checking for that worksheet first
would make sense.

This is a function that I saved from Chip Pearson:

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function

You could include it in your code like:

Do While FNames <> ""
Application.EnableEvents = False
Set mybook = Workbooks.Open(FNames)
Application.EnableEvents = True

If WorksheetExists("tearsheet", mybook) Then
Set sourceDescription = mybook.Worksheets("TearSheet").Range("K20")
Set destDescription = basebook.Worksheets("TearSheet").Range("K20")
sourceDescription.Copy destDescription
End If

mybook.Close savechanges:=False
FNames = Dir()
Loop

But it looked like it should be closing myBook--not basebook.

And since you were just copying from that workbook, why should it be saved.

On the other hand, you could add:

BaseBook.save

Right at the end of the code (if you wanted to save that workbook).

===
But that leads to another question.

> Set sourceDescription = mybook.Worksheets("TearSheet").Range("K20")
> Set destDescription = basebook.Worksheets("TearSheet").Range("K20")

Aren't you just overwriting the same cell each time you retrieve a value from
those other workbooks???

Jason wrote:
>
> Ron,
>
> Your code works beautifully, however, I need to twist it around a little
> bit, and when I do, I get a run time error. What I want to do is instead of
> having the base file go through an entire directory and update from the base
> file to all other files, I want to have the base file open to start, have the
> code run so that it opens only one workbook (I'll probably just leave one
> workbook in the directory at a time for simplification), and copy from the
> newly opened file to the base file. So in essence, I'm doing something
> similar but have just switched around the copying procedures. When I do
> this, I get the run time error "Run Time Error '1004': Select method of
> range class failed". The code runs until it hits the line "Set
> sourceDescription = mybook...". Any idea of how to get around this, code is
> posted below. Thanks.
>
> Jason
>
> Sub Transfer()
>
> Dim basebook As Workbook
> Dim mybook As Workbook
> Dim sourceDescription As Range
> Dim destDescription As Range
>
> Dim FNames As String
> Dim MyPath As String
> Dim SaveDriveDir As String
>
> SaveDriveDir = CurDir
> MyPath = "P:\PRT\CREDITS\CreditReview"
> ChDrive MyPath
> ChDir MyPath
> FNames = Dir("*.xls")
> If Len(FNames) = 0 Then
> MsgBox "No files in the Directory"
> ChDrive SaveDriveDir
> ChDir SaveDriveDir
> Exit Sub
> End If
> Application.ScreenUpdating = True
> Set basebook = ThisWorkbook
> Do While FNames <> ""
> Application.EnableEvents = False
> Set mybook = Workbooks.Open(FNames)
> Application.EnableEvents = True
>
> Set sourceDescription = mybook.Worksheets("TearSheet").Range("K20")
> Set destDescription = basebook.Worksheets("TearSheet").Range("K20")
> sourceDescription.Copy destDescription
>
> basebook.Save
> basebook.Close True
> FNames = Dir()
> Loop
> ChDrive SaveDriveDir
> ChDir SaveDriveDir
> Application.ScreenUpdating = True
> End Sub
>
> "Ron de Bruin" wrote:
>
> > Hi Jason
> >
> > If you use the sheet option it will copy the sheet code also.
> > For changing the module code look at Chip Pearson's site
> > http://www.cpearson.com/excel/vbe.htm
> >
> > If you need more help post back
> >
> >
> > --
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> >
> > "Jason" <[email protected]> wrote in message news:[email protected]...
> > > Thanks Ron, this is very helpful. A follow up question... Is it possible to
> > > also transfer VBA modules as well. For example, assume I have a module which
> > > contains the old code, but I have since updated that code in the same module,
> > > can I use code to delete the old module and transfer the new module to the
> > > old workbook. If so, what would the code look like? Also, I assume if I
> > > have code in a worksheet and I use the code you provide to move the
> > > worksheet, the associated code will also get transferred with it. Is that
> > > correct? Thanks.
> > >
> > > "Ron de Bruin" wrote:
> > >
> > >> Hi Jason
> > >>
> > >> Start here
> > >> http://www.rondebruin.nl/copy4.html
> > >> --
> > >> Regards Ron de Bruin
> > >> http://www.rondebruin.nl
> > >>
> > >>
> > >>
> > >> "Jason" <[email protected]> wrote in message news:[email protected]...
> > >> >I have a file which serves as a master template. Over time, I have used this
> > >> > for several workbooks which contain financial data on companies, and
> > >> > therefore now have over 100 different workbooks based off this template. The
> > >> > problem now is that the master template had to be updated, and I need the
> > >> > changes to be reflected in each of the workbooks saved under the old
> > >> > template.
> > >> >
> > >> > The updates that need to be made are fairly simple, and mostly include new
> > >> > values or formulas in cells. What I would like to do is have the master
> > >> > template workbook open, and then have some code that will go through all
> > >> > workbooks in a directory one by one, open each workbook, and copy certain
> > >> > certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
> > >> > for this example) from the master template to the old file with the outdated
> > >> > template.
> > >> >
> > >> > Is this difficult to do. I would appreciate any code that could be provided
> > >> > to accomplish this. Thanks.
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

JAson or anyone, i to have several worksheets in excell, i updated formulas
in my main template is there an easy way to update the formulas in the old
worksheets easily or do i have to manually update each one. i read answers to
jasons problem but i dont really understand them. help

"Jason" wrote:

> I have a file which serves as a master template. Over time, I have used this
> for several workbooks which contain financial data on companies, and
> therefore now have over 100 different workbooks based off this template. The
> problem now is that the master template had to be updated, and I need the
> changes to be reflected in each of the workbooks saved under the old
> template.
>
> The updates that need to be made are fairly simple, and mostly include new
> values or formulas in cells. What I would like to do is have the master
> template workbook open, and then have some code that will go through all
> workbooks in a directory one by one, open each workbook, and copy certain
> certain cells (let's assume Cell A1 on Worksheet 1 and Cell B2 on Worksheet 2
> for this example) from the master template to the old file with the outdated
> template.
>
> Is this difficult to do. I would appreciate any code that could be provided
> to accomplish this. Thanks.