I am using the macro mentioned in the subject. The problem is that it will only insert a row if the active cell is in the last filled row. I want the new row to be inserted after the last filled row, but there are multiple users of this worksheet and there is no way of knowing where they will leave the active cell. Is there a way for the active cell to be moved to the last filled row when the worksheet is closed? That way the active cell would be in the right place when the worksheet is opened and the new row would be inserted in the right place. Or something like that

Thanks

Leslie Barberi

Leslie,

There is no standard InsertRowsAndFillFormulas macro, so posting the code
would help.

If you are game to try modifying it sight-unseen, then you could start the
macro with the line

Cells(65536, ActiveCell.Column).End(xlUp).Select

and that may work, since it selects the last filled cell in the current
column.

HTH,
Bernie
MS Excel MVP

"Leslie Barberie" wrote in message
...
I am using the macro mentioned in the subject. The problem is that it
will only insert a row if the active cell is in the last filled row. I want
the new row to be inserted after the last filled row, but there are multiple
users of this worksheet and there is no way of knowing where they will leave
the active cell. Is there a way for the active cell to be moved to the last
filled row when the worksheet is closed? That way the active cell would be
in the right place when the worksheet is opened and the new row would be
inserted in the right place. Or something like that?

Thanks.

Leslie Barberie

Bernie

I apologize for not including the code....senior moment. It is code that I copied from David McRitchie's website

Private Sub Workbook_Open(
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.ht
' Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Intege
' row selection based on active cell -- rev. 2000-09-02 David McRitchi
' ActiveCell.Offset(1, 0).Selec

ActiveCell.EntireRow.Select 'So you do not have to preselect entire r
vRows =

' If vRows < 1 The
' vRows = Application.InputBox(prompt:=
' "How many rows do you want to add?", Title:="Add Rows",
' Default:=1, Type:=1) 'type 1 is numbe
' If vRows = False Then Exit Su
' End I

'if you just want to add cells and not entire row
'then delete ".EntireRow" in the following lin

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheet
Dim sht As Worksheet, shts() As String, i As Intege
ReDim shts(1 To Worksheets.Application.ActiveWorkbook.
Windows(1).SelectedSheets.Count
i =
For Each sht In
Application.ActiveWorkbook.Windows(1).SelectedShee t
Sheets(sht.Name).Selec
i = i +
shts(i) = sht.Nam

Selection.Resize(rowsize:=2).Rows(2).EntireRow.
Resize(rowsize:=vRows).Insert Shift:=xlDow

Selection.AutoFill Selection.Resize(
rowsize:=vRows + 1), xlFillDefaul

On Error Resume Next 'to handle no constants in range -- John McKee 2000/02/0
' to remove the non-formulas -- 1998/03/11 Bill Manvill
Selection.Offset(1).Resize(vRows).EntireRow.
SpecialCells(xlConstants).ClearContent
Next sh
Worksheets(shts).Selec

End Su

Leslie Barberie

Try this: Change

ActiveCell.EntireRow.Select 'So you do not have to preselect entire row

To

Range(65536, ActiveCell.Column).End(xlUp).EntireRow.Select 'So....

HTH,
Bernie
MS Excel MVP

"Leslie Barberie" wrote in message
...
Bernie,

I apologize for not including the code....senior moment. It is code that
I copied from David McRitchie's website.

Private Sub Workbook_Open()
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
' ActiveCell.Offset(1, 0).Select

ActiveCell.EntireRow.Select 'So you do not have to preselect entire ro
vRows = 1

' If vRows < 1 Then
' vRows = Application.InputBox(prompt:= _
' "How many rows do you want to add?", Title:="Add Rows", _
' Default:=1, Type:=1) 'type 1 is number
' If vRows = False Then Exit Sub
' End If

'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
Windows(1).SelectedSheets.Count)
i = 0
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedShee ts
Sheets(sht.Name).Select
i = i + 1
shts(i) = sht.Name

Selection.Resize(rowsize:=2).Rows(2).EntireRow. _
Resize(rowsize:=vRows).Insert Shift:=xlDown

Selection.AutoFill Selection.Resize( _
rowsize:=vRows + 1), xlFillDefault

On Error Resume Next 'to handle no constants in range -- John McKee
2000/02/01
' to remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select

End Sub

Leslie Barberie

I made the code change and I'm getting Run Time Error 1004 - Method Range of Object_Global failed.

Leslie,

As you should have, since I was brain-dead <g.

Simply change the word Range to Cells: i.e., change

Range(65536, .....

to

Cells(65536, .....

Sorry about that,
Bernie



"Leslie Barberie" wrote in message
...
I made the code change and I'm getting Run Time Error 1004 - Method Range
of Object_Global failed.

Thanks, Bernie. That worked. And, by the way, that brain-dead thing.....I could use a fix for that, too

Leslie Barberie
----- Bernie Deitrick wrote: ----

Leslie

As you should have, since I was brain-dead <g

Simply change the word Range to Cells: i.e., chang

Range(65536, ....

t

Cells(65536, ....

Sorry about that
Berni



"Leslie Barberie" wrote in messag
..
I made the code change and I'm getting Run Time Error 1004 - Method Rang
of Object_Global failed