Add New Row to Multiple Workbooks

[Guest]

Using Excel 2000, I have 8 workbooks, one sheet each, which all need the same
row added to the same place in each workbook, but it could be a different
location each time the rows are added (make sense?). For example, today I
may need to add a new row 5 to all 8 workbooks, but tomorrow I may ned to add
a new row 20 to all 8 workbooks. Is there a way to do this quickly, without
having to open each one and manually add the row in?

Any help is appreciated.

 

[Ron de Bruin]

You can start with this code example
http://www.rondebruin.nl/copy4.htm
Post back if you need more help

 

[Guest]

Sub UpdateWorkbooks()
Dim sPath as String, v as Variant
Dim bk as Workbook, i as Long
Dim ans as Variant
rw as Long

ans = Application.Inputbox("Enter the row to add",type:=1)
if ans = false then exit sub
rw = clng(ans)
sPath = "C:\Myfolder\"
v = array("Book1.xls","mybook.xls","yourbook.xls", . . .)
for i = lbound(v) to ubound(v)
set bk = workbook.Open(sPath & v(i))
bk.Worksheets(1).Rows(j).Insert
bk.Close Savechanges:=True
Next
End Sub

 

[Guest]

Thank you for the suggestion but I know little to nothing about macros and
when I run this, the row of code "rw As Long" is highlighted and it give me
the following error:

Compile error:
Statement invalid outside Type block

Do I have to make any changes to the code before I use it?

Many thanks for your patience,

 

[Guest]

I got interrupted and sent it before I had a chance to look it over. That is
caused by a typo. You should just need to put your workbook names in inplace
of the a.xls, b.xls and change the spath= to refer to the folder where you
workbooks are located. You might want to make a dummy path and make a copy
of them there to insure the macro does what you want and doesn't screw up the
originals.

Sub UpdateWorkbooks()
Dim sPath As String, v As Variant
Dim bk As Workbook, i As Long
Dim ans As Variant
Dim rw As Long

ans = Application.InputBox("Enter the row to add", Type:=1)
If ans = False Then Exit Sub
rw = CLng(ans)
sPath = "C:\Myfolder\"
v = Array("a.xls", "b.xls", "c.xls", _
"d.xls", "e.xls", "f.xls", _
"g.xls", "h.xls")
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(Filename:=sPath & v(i))
bk.Worksheets(1).Rows(rw).Insert
bk.Close Savechanges:=True
Next
End Sub

after making those changes, then I would expect it to run.

It should be place in a general module (in the VBE [alt+F11], Insert=>Module)

 

[Guest]

Thank you SO much. It is working perfectly. I really appreciate the help.
--
LPS

I got interrupted and sent it before I had a chance to look it over. That is
caused by a typo. You should just need to put your workbook names in inplace
of the a.xls, b.xls and change the spath= to refer to the folder where you
workbooks are located. You might want to make a dummy path and make a copy
of them there to insure the macro does what you want and doesn't screw up the
originals.

Sub UpdateWorkbooks()
Dim sPath As String, v As Variant
Dim bk As Workbook, i As Long
Dim ans As Variant
Dim rw As Long

ans = Application.InputBox("Enter the row to add", Type:=1)
If ans = False Then Exit Sub
rw = CLng(ans)
sPath = "C:\Myfolder\"
v = Array("a.xls", "b.xls", "c.xls", _
"d.xls", "e.xls", "f.xls", _
"g.xls", "h.xls")
For i = LBound(v) To UBound(v)
Set bk = Workbooks.Open(Filename:=sPath & v(i))
bk.Worksheets(1).Rows(rw).Insert
bk.Close Savechanges:=True
Next
End Sub

after making those changes, then I would expect it to run.

It should be place in a general module (in the VBE [alt+F11], Insert=>Module)

--
Regards,
Tom Ogilvy

Thank you for the suggestion but I know little to nothing about macros and
when I run this, the row of code "rw As Long" is highlighted and it give me
the following error:

Compile error:
Statement invalid outside Type block

Do I have to make any changes to the code before I use it?

Many thanks for your patience,