Automatically copy formulas to new rows added.
 

How do I get a form (that will be protected) using a macro to add additional
lines to automatically copy the formulas above to the new added lines without
adding another macro?
--
Lynn

Automatically copy formulas to new rows added.
 

Try
Sub test()
Dim InsertionPoint As Range, rg As Range
Dim colToCheck As String
Dim expandBy As Long
Dim wsh As Worksheet
Dim StartRow As Long
Dim ws As Worksheet

'Unprotect sheet
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Unprotect Password:="Password"
Next ws

Set wsh = ActiveSheet
colToCheck = "A"
expandBy = 25
'Find last currently used row in column colToCheck
Set InsertionPoint = wsh.Range(colToCheck & 65536).End(xlUp).Offset(1,
0).EntireRow
StartRow = InsertionPoint.Row - 1

'Insert rows
Set rg = InsertionPoint.Resize(expandBy)
rg.Insert

Range("C" & StartRow).Resize(1, 2).Copy _
Range("C" & StartRow).Resize(expandBy + 1, 2)

'Protect Sheet
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="Password"
Next ws


End Sub


"LCN" wrote:

How do I get a form (that will be protected) using a macro to add additional
lines to automatically copy the formulas above to the new added lines without
adding another macro?
--
Lynn

Automatically copy formulas to new rows added.
 

Hi,

why don't you show us the code you currently have. And how many lines do
you want to add at any one time, or is it just one line? And are you really
asking how to fill the formulas down but leave the remainder of the lines
unchanged?

For example, if you have protected the spreadhsheet using code and the
userinterfaceonly:=true option and you want to extend the formulas down one
row and these formulas are in columns D:H. Then

Bot = Range("D1").End(xlDown).Row
Range("D" & Bot & ":H" & Bot + 1).FillDown

Might do it.

If this helps, please click the Yes button

Cheers,
Shane Devenshire
"LCN" wrote:

How do I get a form (that will be protected) using a macro to add additional
lines to automatically copy the formulas above to the new added lines without
adding another macro?
--
Lynn

Automatically copy formulas to new rows added.
 

I couldn't get this to work, is it possible for me to forward you my form for
you to review?
--
Lynn


"Eduardo" wrote:

Try
Sub test()
Dim InsertionPoint As Range, rg As Range
Dim colToCheck As String
Dim expandBy As Long
Dim wsh As Worksheet
Dim StartRow As Long
Dim ws As Worksheet

'Unprotect sheet
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Unprotect Password:="Password"
Next ws

Set wsh = ActiveSheet
colToCheck = "A"
expandBy = 25
'Find last currently used row in column colToCheck
Set InsertionPoint = wsh.Range(colToCheck & 65536).End(xlUp).Offset(1,
0).EntireRow
StartRow = InsertionPoint.Row - 1

'Insert rows
Set rg = InsertionPoint.Resize(expandBy)
rg.Insert

Range("C" & StartRow).Resize(1, 2).Copy _
Range("C" & StartRow).Resize(expandBy + 1, 2)

'Protect Sheet
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="Password"
Next ws


End Sub


"LCN" wrote:

How do I get a form (that will be protected) using a macro to add additional
lines to automatically copy the formulas above to the new added lines without
adding another macro?
--
Lynn