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 |
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 |
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 |
All times are GMT +1. The time now is 01:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com