Help with VBA scipt

[Jrambo75]

Hello,

I found this thread and it was spot on for what I needed. However, I'm using the script below for adding notes for invoices that are then pulled into a PowerPivot, but it hangs on Pivot refresh if I put more than one note per invoice number (I'm using the invoice number as a relation between two tables). Any suggestions?

Sub Clear()

Application.ScreenUpdating = False

Dim NextRow As Range
Set NextRow = Sheets("notes").Range("A19").End(xlUp).Offset(1, 0)
Sheets("Aging").Range("e2:g2").Copy
NextRow.PasteSpecial (xlValues)

Dim NextRow2 As Range

Sheets("Aging").Range("e2:g2").ClearContents


Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Dan_W]

When you say it hangs on Pivot refresh, does excel still appear to work (but the data in the Pivot table doesn't refresh)? I ask, because the likely culprit is probably the line:

Application.ScreenUpdating = False

You could try commenting the line out (by putting an apostrophe ' at the start of the line and see if that helps...?

 

[Jrambo75]

Hey Dan,

I tried your suggestion but it is still giving me the same message. It has to do with the one to many relationships in the tables:

 

[Jrambo75]

I think the solution is to replace the old "note" with the most recent. Any help with that code would be greatly appreciated.