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

http://www.mvps.org/dmcritchie/excel/insrtrow.htm
Check this out

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" <[email protected] (no spam)> a écrit dans le message de
news:[email protected]...
> 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