Event Macro - propogate formula

[jct]

I am trying to use an event macro referenced in another thread,

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy target.Offset(1).EntireRow
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub

(credit to: http://www.mvps.org/dmcritchie/excel/insrtrow.htm)

but am getting a debug message on the following line:
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents

I need clarification. Any help is appreciated.
Thanks,
Janice

[JE McGimpsey]

If there are no constants, SpecialCells will throw an error. One
modification:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
With Target.EntireRow
.Offset(1, 0).Insert
.Copy .Offset(1, 0).Cells
On Error Resume Next
.Offset(1, 0).SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End With
End Sub




In article <[email protected]>,
jct <[email protected]> wrote:

> but am getting a debug message on the following line:
> Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
>
> I need clarification. Any help is appreciated.

[David McRitchie]

Hi John,
Thanks for the correction, I've updated several event macros
on my insrtrow.htm page.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"JE McGimpsey" <[email protected]> wrote...
> If there are no constants, SpecialCells will throw an error. One
> modification:

[jct]

Thanks, that worked. I'm feeling around with macros and learning as I go.
Maybe you know if there's a better way to do what I'm trying to accomplish...

I want to insert column A:C of a pivot table on Sheet1 into Sheet2, which
contains additional formulas. I tried just inserting the same pivot table in
Sheet2, but the formulas do not automatically fill in on the adjacent cells
(D:M). Any ideas?

What I'm doing now is updating the pivot table in Sheet1 and using the
double-click event macro to fill in any additional rows. Clumsy, but it works.

Thanks,
Janice

"JE McGimpsey" wrote:

> If there are no constants, SpecialCells will throw an error. One
> modification:
>
> Private Sub Worksheet_BeforeDoubleClick( _
> ByVal Target As Range, Cancel As Boolean)
> Cancel = True 'Eliminate Edit status due to doubleclick
> With Target.EntireRow
> .Offset(1, 0).Insert
> .Copy .Offset(1, 0).Cells
> On Error Resume Next
> .Offset(1, 0).SpecialCells(xlConstants).ClearContents
> On Error GoTo 0
> End With
> End Sub
>
>
>
>
> In article <[email protected]>,
> jct <[email protected]> wrote:
>
> > but am getting a debug message on the following line:
> > Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
> >
> > I need clarification. Any help is appreciated.
>