InsertRowsAndFillFormulas()

[Hans Knudsen]

http://www.geocities.com/davemcritchie/excel/excel.htmI

Will someone help me with a little bit more code to the above useful
"InsertRowsAndFillFormulas" macro so that the specified number of rows are
inserted in both sheet(2) and sheet(5). The prompt for number of rows should
appear only once.

Hans Knudsen

[Gary Brown]

Using the code on Dave McRitchie's web site (FYI, there's no "I" at the end
of the address, it's "Excel.htm"), here's the adjusted code. You must first
select the two worksheets so that they are grouped. [Click on the tab for
sheet(2), highlighting it, then hold down the Ctrl key and click on the tab
for sheet(5). At this point, the tabs for both sheet(2) AND sheet(5) should
be highlighted]. Then run the macro.
HTH,
Gary L. Brown
Kinnes...@Snet.net

'/=======Start of Code==========================/
Sub InsertRowsAndFillFormulas()
' Re: Insert Rows -- 1997/09/24 Mark Hill
' The modified macro is described in
' http://www.geocities.com/davemcritchie/excel/insrtrow.htm
Dim vRows As Integer
Dim sht As Worksheet

' row selection based on active cell --
' rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select
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
'if you just want to add cells and not entire rows
' then delete ".EntireRow" in the following line

'insert rows on grouped worksheets
' rev. 2001-01-17 Gary Brown
For Each sht In _
Application.ActiveWorkbook.Windows(1).SelectedSheets
Sheets(sht.Name).Select
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 remove the non-formulas -- 1998/03/11 Bill Manville
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht

End Sub
'/=======End of Code==========================/

Hans Knudsen <hans.k...@mail.tele.dk> wrote in message
news:ekzQLvKgAHA.716@tkmsftngp03...

[David McRitchie]

Thanks Gary,
Going through the selected sheets was going to be my choice
but then I thought I'd try to find out why it didn't work. The
selections of the entire row to start with gets made on all
sheets but not the inserting rows.

I was looking at this macro and some other existing macros
with grouped sheets and some freshly recorded macros with
grouped sheets, very little difference, yet mine don't
work with grouped sheets and the newly recorded ones did.

So there is probably a simpler solution around. This macro
inserts rows below the selection and the ones I recorded to
it in the conventional Excel manner above.

HTH,
David McRitchie, Microsoft MVP - Excel (site changed 2000-04-15)
My Excel Macros: http://www.geocities.com/davemcritchie/excel/excel.htm
Search Page: http://www.geocities.com/davemcritchie/excel/search.htm

"Gary Brown" <Kinnes...@Snet.net> wrote in message
news:u2L4MXMgAHA.948@tkmsftngp03...

[David McRitchie]

There is a problem with the code, in that when the macro
ends, only one sheet is selected. I can't figure out how
to put the selected sheets into an array to be restored
at the end. David McRitchie
================================='

code modifications by
Gary L. Brown
Kinnes...@Snet.net

Sheets(Array(shts)).Select '/// something like this needed

[David McRitchie]

Here is a correction that reselects the original selection
of sheets. David McRitchie

> code modifications by
> Gary L. Brown
> Kinnes...@Snet.net
>
> '/=======Start of Code==========================/
Sub InsertRowsAndFillFormulas()

' Documented: http://www.geocities.com/davemcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill <mark...@charm.net.noSpam>
Dim vRows As Integer

' row selection based on active cell -- rev. 2000-09-02 David McRitchie

ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
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
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line

'rev. 20001-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).SelectedSheets
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

[Gary Brown]

Thanks, Dave
Gary

--
Gary L. Brown
Kinnes...@Snet.net

David McRitchie <DMcRi...@msn.com> wrote in message
news:OyXo4JOgAHA.2024@tkmsftngp05...