Inserting rows macro
 

Hi,
I have this code, as below, coming from David McRitchie´s webpage. It works
great, (Thanks, David), but I need help to extend it, so that it works for
my needs.

Now, I have a workbook with several simular sheets. It´s one sheet for every
month of the year and some other sheets.
In each "monthsheet" I have formulas that refers to one "main sheet" that
contains all the information to be .

I wonder if you can help me to create a macro that always starts in the
"main sheet" and then inserts the same rows in all the other sheets at the
same time, and also change the formulas from (i.e =c6 to =c7), and not as
the formula is now, as it just duplicates. (i.e=c6 to =c6). All the sheets
are protected with password, therefore I´ts also necessary to unprotect, and
then protect the sheets in that same code.

I hope you understand what I´m trying to reach for.

Code:

Sub InsertRowsAndFillFormulas(Optional vRows As Long)
' 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.EntireRow.Select 'So you do not have to preselect entire row
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. 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).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

//Regards,
Thomas

Inserting rows macro
 

Hi Jonsson
Provided that you want to insert a row at the same place on ALL you
worksheets, you could use the following code.

'Select multiple worksheets.
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

Select and insert the row that you wish
Sheets("Sheet1").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

One row has been inserted into each of the worksheets.
Hope this help

--
Message posted from http://www.ExcelForum.com

Inserting rows macro
 

All the sheets are protected with password, therefore I´ts also necessary
to unprotect, and then protect the sheets in that same code.

Assuming they were all unprotected at the time.

--
Don Guillett
SalesAid Software

"liddlem " wrote in message
...
Hi Jonsson
Provided that you want to insert a row at the same place on ALL your
worksheets, you could use the following code.

'Select multiple worksheets.
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

Select and insert the row that you wish
Sheets("Sheet1").Activate
Rows("3:3").Select
Selection.Insert Shift:=xlDown

One row has been inserted into each of the worksheets.
Hope this helps


---
Message posted from http://www.ExcelForum.com/