Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
insert rotating formaula
i have a macro which inserts new rows accross 12 pages in the position the
user wants the new row to be inputted. what i need to to do is copy a formula from the row above into the new line (this will always be in col B) or to insert a formula (=Overview!C39) but where the c39 changes to whateve the new row number is have tried using variables set the range of each new cell but with no luck thanks in advance Sub INSERT_ROW() 'Confirm new line Dim sh As Worksheet X = InputBox("Please Enter The Line Number Where You Want To Enter New Staff") Application.ScreenUpdating = False 'Unprotect Sheet Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="lockout" Next n ' insert Row For Each sh In Worksheets(Array("Jan", "Feb", "March", "april", "may", "June", "July", "Aug", "Sep", "Oct", "Nov", "Dec", "Overview")) sh.Rows(X).INSERT Next sh 'Create Log Open "c:\LogFolder\sheet log.txt" For Append As #1 Print #1, Format(Now, "mm/dd/yy hh:nn") & " Holiday Monitor " & Application.UserName & " Inserted New Employee at line "; X Close #1 'reestablist passwords Dim k As Single For k = 1 To Sheets.Count Sheets(k).Protect Password:="lockout" Next k Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
insert rotating formaula
http://www.mvps.org/dmcritchie/excel/insrtrow.htm Check this out -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=522125 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
insert rotating formaula
The following will copy formula in column B from row above.
Won't work if X=1 PS: I simplified your code so that Unprotect/Protect takes place in the same loop as Insert HTH -- AP '---------------------------------------------------- Sub INSERT_ROW() 'Confirm new line Dim sh As Worksheet X = InputBox( _ "Please Enter The Line Number Where You Want To Enter New Staff ") Application.ScreenUpdating = False ' insert Row With Worksheets("Jan") For Each sh In Worksheets( _ Array("Jan", "Feb", "March", "april", "may", _ "June", "July", "Aug", "Sep", "Oct", _ "Nov", "Dec", "Overview")) sh.Unprotect Password:="lockout" sh.Rows(X).Insert sh.Cells(X, "B").Formula = sh.Cells(X - 1, "B").Formula sh.Protect Password:="lockout" 'Next sh End With 'Create Log Open "c:\LogFolder\sheet log.txt" For Append As #1 Print #1, _ Format(Now, "mm/dd/yy hh:nn") & _ " Holiday Monitor " & _ Application.UserName & _ " Inserted New Employee at line "; X Close #1 Application.ScreenUpdating = True End Sub '--------------------------------------------- "Rich Mcc" (no spam) a écrit dans le message de ... i have a macro which inserts new rows accross 12 pages in the position the user wants the new row to be inputted. what i need to to do is copy a formula from the row above into the new line (this will always be in col B) or to insert a formula (=Overview!C39) but where the c39 changes to whateve the new row number is have tried using variables set the range of each new cell but with no luck thanks in advance Sub INSERT_ROW() 'Confirm new line Dim sh As Worksheet X = InputBox("Please Enter The Line Number Where You Want To Enter New Staff") Application.ScreenUpdating = False 'Unprotect Sheet Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="lockout" Next n ' insert Row For Each sh In Worksheets(Array("Jan", "Feb", "March", "april", "may", "June", "July", "Aug", "Sep", "Oct", "Nov", "Dec", "Overview")) sh.Rows(X).INSERT Next sh 'Create Log Open "c:\LogFolder\sheet log.txt" For Append As #1 Print #1, Format(Now, "mm/dd/yy hh:nn") & " Holiday Monitor " & Application.UserName & " Inserted New Employee at line "; X Close #1 'reestablist passwords Dim k As Single For k = 1 To Sheets.Count Sheets(k).Protect Password:="lockout" Next k Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to insert a picture using formulas? | Excel Worksheet Functions | |||
How do I insert a reference to lookup and insert a name | New Users to Excel | |||
insert a JPEG into EXCEL 2002 | Excel Discussion (Misc queries) | |||
insert picture | Excel Discussion (Misc queries) | |||
Challenging Charting | Charts and Charting in Excel |