Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make the changes to the Parent applicable to all copied files
I work with the job of entering datas in preset formats of several
compaines. I have a table with number of rows and columns in a file of MS-excel. I have made this as a PARENT and copied several such files for data entry. Now, after I have completed most of the job, I found that I forgot to add the sum column in the parent Excel file. Now, I want to add a column to the parent file such that once I have added (and/or removed ) the column, this change be made to all the files that I have made a copy of. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make the changes to the Parent applicable to all copied files
open the VBA editor (ALT+F11)
add a new module ( INSERT / MODULE) and paste in the following code. It will sum column B, C,D ... while there is a heading in row 1, of Sheet1 you can run this against all your workbooks....(AddSums) or run SumBooks that runs AddSums for each book in a specified folder. Again, it will assume that the sheet os called sheet1 you may want to change values appropriately Option Explicit Sub AddSums() Dim cl As Range Set cl = Worksheets("sheet1").Range("B1") Do Until cl.Value = "" With cl.End(xlDown).Offset(1) .FormulaR1C1 = "=SUM(R[-1]C:R1C)" End With Set cl = cl.Offset(, 1) Loop End Sub Sub SumBooks() Dim sFile As String Dim wb As Workbook Const sPath As String = "S:|temp\" sFile = Dir(sPath & "*.xls") Do While sFile < "" Set wb = Workbooks.Open(sPath & sFile) AddSums wb.Close True ' close & save Set wb = Nothing sFile = Dir(sPath & "*.xls") Loop End Sub "Subodh" wrote: I work with the job of entering datas in preset formats of several compaines. I have a table with number of rows and columns in a file of MS-excel. I have made this as a PARENT and copied several such files for data entry. Now, after I have completed most of the job, I found that I forgot to add the sum column in the parent Excel file. Now, I want to add a column to the parent file such that once I have added (and/or removed ) the column, this change be made to all the files that I have made a copy of. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I make a worksheet that can not be copied | Excel Programming | |||
Do VLOOKUPs require the applicable other files to be open? | Excel Discussion (Misc queries) | |||
how to default WritePassword under excel workbook property for any files as long as those files are the offsprings of the parent file | Excel Programming | |||
How can I make the copied range from another worksheet independently | Excel Programming | |||
copy subfolders, replace text in files and save files in copied subfolders | Excel Programming |