Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum formulas do NOT expand when rows added ! | Excel Worksheet Functions | |||
how do I lock formulas, but still allow rows to be added or delet. | Excel Worksheet Functions | |||
Macro to automatically add rows and copy formulas | Excel Discussion (Misc queries) | |||
Automatically copy formulas/functions to inserted rows. | Excel Worksheet Functions | |||
Can I automatically extend formulas to added lines in Excel 2002? | Excel Discussion (Misc queries) |