Expert: Tom Ogilvy - 7/8/2009

Question
QUESTION: Hi Tom,  Is there a formula/macro that would enable support staff to enter a new row in a worksheet but only copy the formulas from the previous row?  I found a way to insert a new row even when the worksheet is protected, but it copies everything including the formulas, which is good, but it alos means a lot of deleting data in cells before inserting new data.  The macro code I found/used is below, but as I said it copies everyhting when inserting a new row instead of just the formulas.

   Activesheet.Unprotect ("password")
   Selection.Copy
   Selection.Insert Shift:=xlDown
   Application.CutCopyMode = False
   Activesheet.Protect Password:="password"


ANSWER: Dave,

Well you could modify that

Sub abc()
ActiveSheet.Unprotect ("password")
  Selection.Copy
  Selection.Insert Shift:=xlDown
  Selection.SpecialCells(xlConstants).ClearContents
  Application.CutCopyMode = False
  ActiveSheet.Protect Password:="password"

End Sub

or

Sub abc()
ActiveSheet.Unprotect ("password")
  Selection.Copy
  Selection.Insert Shift:=xlDown
  Selection.Offset(1,0).SpecialCells(xlConstants).ClearContents
  Application.CutCopyMode = False
  ActiveSheet.Protect Password:="password"
End Sub

depending on from which row you want the constants cleared.

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Tom,

This is very neat.  Thanks.

Can it also be done so that:

1) it inserts the new row below the one you selected at the time the macro is run? right now either example you sent is inserting the new row above the selected row.  I've tried finding an options/preferences to fix it but no luck.

2) can it be done so that if they forget to select a whole row (just in a single cell when they run the macro) that it will still insert a new row below with everything but the formulas cleared?

Answer
Dave,

Try this (worked for me)

Sub abc()
Dim r As Range
ActiveSheet.Unprotect ("password")
 Set r = Selection.EntireRow
 r.Copy
 r.Offset(1, 0).Insert Shift:=xlDown
 r.Offset(1, 0).SpecialCells(xlConstants).ClearContents
 Application.CutCopyMode = False
 ActiveSheet.Protect Password:="password"

End Sub

--
Regards,
Tom Ogilvy