Insert rows using a macro button.

[Nemesis696]

Hi!

I'm trying to complete my small project that will become very handy at my daily work. On a primary worksheet - "Info" i need to copy/paste row "21" number of times and maintain formulas from cells "AA21" and "AF21" in every newly created row.
I have found this code :whistle::

source: http://dmcritchie.mvps.org/excel/insrtrow.htm


Private Sub CommandButton1_Click()
Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)

Dim x As Long
ActiveCell.EntireRow.Select
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1)
If vRows = False Then Exit Sub
End If

Dim sht As Worksheet, shts() As String, i As Long
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

x = Sheets(sht.Name).UsedRange.Rows.Count

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
Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht
Worksheets(shts).Select
End Sub

and attached it to the button on my worksheet.
Now I have encountered the following problems, which I hoped to get help with: pray2::

1. Formulas in in cells "AF" in every copy/pasted row are wrong. For example in row from which i'm copying from, formula looks like this
=IF(S18<>0%;SUM(AA21*(1+S18));SUM(P21+AA21))
in next row it must be
=IF(S18<>0%;SUM(AA22*(1+S18));SUM(P22+AA22))
but it has changed to
=IF(S19<>0%;SUM(AA22*(1+S19));SUM(P22+AA22))
How do can I fix it?

2. When i'm working with "sensitive" :cool: documents like this one, I like to protect worksheets with password for further use. After I have locked this worksheet, the VBA code attached to the button gave me an error:banghead:. Can that be fixed?

Thanks in advance!

[GTO]

Hi there,

For the first bit, I think that just making part of the formula absolute would work:


=IF($S$18<>0%,SUM(AA22*(1+$S$18)),SUM(P22+AA22))

For the working w/protected sheets bit, it appears to me that you might be making changes to several sheets at once through SelectedSheets. I do not believe you can unprotect/protect sheets this way. You could maybe use this to give you an idea on how to loop through the selected sheets and unprotect/protect ea sheet as it is being worked on, or, see if .Protect with the UserInterfaceOnly = True will cover.
Sub TEST()
Dim MultSheets
Dim wks As Worksheet

Set MultSheets = ThisWorkbook.Windows(1).SelectedSheets
For Each wks In MultSheets
MsgBox "your code to make cahnges to: " & wks.Name & " , or, maybe Protect w/the UserInterfaceOnly=True"
Next
End Sub

Someone may well come up with a better idea, or leastwise say why I had to use a variant for MultSheets?

Hope that helps,

Mark

[Nemesis696]

GTO many thanks for your replay!

Formula absolution is great function and is new for me :bow:. I will use it in future projects.
I will abandon my macro button for now (until I'll figure out, how to make it work on locked sheets), because properly working "protect sheet" function of this worksheet is a first priority in my project. Just don't want, any not working extra stuff on it.