Help with Event macro.....
 

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?


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=537849

Help with Event macro.....
 

"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




"Celt" wrote
in message
...

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?


--
Celt
------------------------------------------------------------------------
Celt's Profile:
http://www.excelforum.com/member.php...o&userid=19413
View this thread:
http://www.excelforum.com/showthread...hreadid=537849

Help with Event macro.....
 

Thanks very much for your help Chip!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=537849