Make the changes to the Parent applicable to all copied files

  • Thread starter Thread starter Subodh
  • Start date Start date
S

Subodh

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.
 
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
 
Back
Top