Help with Event macro.....

[Celt]

TIA for any help offered!!

I have written the following event macro to copy the entire row above
when a row is insert into the worksheet and paste the formatting and
formulas in the new row.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row.HasFormula = False Then
Run ("BacktoNormal")
Target.Row.Offset(-1, 0).EntireRow.Select
Selection.Copy
ActiveRow.Offset(1, 0).EntireRow.Select
Application.EnableEvents = False
PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Run ("SpecialMode")
Application.EnableEvents = True

End Sub

Excel is giving me an "invalid qualifier" error and highlighting "Row"
in the first instance of "Target.Row". I am not sure what i am gettng
this error. "BacktoNormal" and "SpecialMode" are macros that run on
the entire workbook preventing the paste function.

Could someone set me straight?

 

[Chip Pearson]

"Row" returns a Long integer indicating the row number of a
range. That is why you are having problems with the code

Target.Row.Offset(-1, 0).EntireRow.Select

Get rid of the Row and you should be alright.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




in message
news:[email protected]...

 

[Celt]

Thanks very much for your help Chip!