J
Jonsson
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
' Re: 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).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
End Sub
//Regards,
Thomas
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
' Re: 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).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
End Sub
//Regards,
Thomas