Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Multiple Workbooks
Hi Jason
Start here http://www.rondebruin.nl/copy4.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jason" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Multiple Workbooks
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Multiple Workbooks
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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Multiple Workbooks
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" wrote in message ... 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" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Multiple Workbooks
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" wrote in message ... 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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Multiple Workbooks
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" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Multiple Workbooks
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UPDATING LINKS TO OTHER WORKBOOKS :( | Excel Discussion (Misc queries) | |||
Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
Updating multiple linked workbooks | New Users to Excel | |||
Updating linked workbooks | Excel Discussion (Misc queries) | |||
Updating Old Workbooks | Excel Worksheet Functions |