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.
--
LPS

--
LPS

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LPS" <(E-Mail Removed)> wrote in message news:EE73960C-10E9-4609-AFD7-(E-Mail Removed)...
> 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.
> --
> LPS
>
> --
> LPS

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


"LPS" wrote:

> 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.
> --
> LPS
>
> --
> LPS

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,
--
LPS


"Tom Ogilvy" wrote:

> 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
>
>
> "LPS" wrote:
>
> > 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.
> > --
> > LPS
> >
> > --
> > 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


"LPS" wrote:

> 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,
> --
> LPS
>
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> >
> > "LPS" wrote:
> >
> > > 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.
> > > --
> > > LPS
> > >
> > > --
> > > LPS

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


"Tom Ogilvy" wrote:

> 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
>
>
> "LPS" wrote:
>
> > 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,
> > --
> > LPS
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > 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
> > >
> > >
> > > "LPS" wrote:
> > >
> > > > 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.
> > > > --
> > > > LPS
> > > >
> > > > --
> > > > LPS