Nemesis696
09-26-2011, 04:59 PM
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!
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!