Help with Code in Excel

[truitt20]

I am trying to sum information on sheet1 based on criteria and put it into sheet2. The data in sht1 updates every 10 seconds and everytime it updates I want it to overwrite the data in sht2. Also, I would like to sort the values descending. So far I have this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long, lRow2 As Long, lRow3 As Long, lRow4 As Long, WF As WorksheetFunction
Set WF = Application.WorksheetFunction
If Not Application.Intersect(Target, Range("D")) Is Nothing Then
With Sheet2
lRow2 = .Range("A1").CurrentRegion.Rows.Count
.Cells(lRow2, 1) = Now
.Cells(lRow2, 2) = "United States"
.Cells(lRow2, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "USA", Sheet1.Range("D"))
.Cells(lRow2, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "USA", Sheet1.Range("D")) / Sheets("Notionals").Range("E16").Value
End With
With Sheet2
lRow3 = .Range("A2").CurrentRegion.Rows.Count
.Cells(lRow3, 1) = Now
.Cells(lRow3, 2) = "Japan"
.Cells(lRow3, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "JAP", Sheet1.Range("D"))
.Cells(lRow3, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "JAP", Sheet1.Range("D")) / Sheets("Notionals").Range("E16").Value
End With
With Sheet2
lRow4 = .Range("A3").CurrentRegion.Rows.Count
.Cells(lRow4, 1) = Now
.Cells(lRow4, 2) = "Australia"
.Cells(lRow4, 3) = WF.SumIf(Sheet1.Range("Z:Z"), "AUL", Sheet1.Range("D"))
.Cells(lRow4, 4) = WF.SumIf(Sheet1.Range("Z:Z"), "AUL", Sheet1.Range("D")) / Sheets("Notionals").Range("E16").Value
End With
End If
Set WF = Nothing
End Sub

This outputs this:

1:56:26 PM United States (12,636,980.58) -0.5167%
1:56:26 PM Japan (7,228,409.82) -0.2955%
12:59:50 PM Australia (946,931.92) -0.0387%



This only updates once (upon opening wrkbk). But after that only the last row gets updated from then on out. Any help would be appreciated.

thanks

jt

 

[firefytr]

If you want to overwrite the data, hardcode the range. You're using a variable..

lRow2 = .Range("A1").CurrentRegion.Rows.Count

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[truitt20]

once putting in this amendment it still only updates the third row (Australia).

 

[firefytr]

Putting what in the code? What did you change? If you have certain cells you want changed, you either need them hardcoded, (range) named, hardcoded or to find them dynamically (ie the Find method).

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci