John,

Thanks for the quick reply. The code is below.

I closed Excel and restarted. Now it works fine!

Guess I had some corruption...

steve


=================================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lrow As Long, prow As Long, pcol As Long, mnth As Integer, yr As Integer

If Target.Column 4 Then
If Target.Row 3 Then
Application.ScreenUpdating = False
Application.EnableEvents = False

' Last row of data
lrow = Cells(Rows.Count, "A").End(xlUp)
' Present column
pcol = Target.Column
' New Month & Year
mnth = Month(Cells(1, pcol - 1)) + 1
yr = Year(Cells(1, pcol - 1))
If mnth = 13 Then
mnth = 1
yr = yr + 1
End If
' Enter column total and month on Summary sheet
Sheets("Summary").Cells(3, pcol).Value =
WorksheetFunction.Sum(Sheets("Corr").Range(Cells(3 , pcol), Cells(lrow,
pcol)))
Sheets("Summary").Cells(1, pcol).Value = Sheets("Corr").Cells(1, pcol).Value
Sheets("Summary").Cells(4, pcol).FormulaR1C1 = "=R5C1*R1C+R6C1"

' Enter new month & year on sheet
Sheets("Corr").Cells(1, pcol).Formula = mnth & "/1/" & yr

' set names for chart
pcol = ActiveSheet.Cells(1, 256).End(xlToLeft).Column + 1

ActiveWorkbook.Names.Add Name:="cCorr",
RefersToR1C1:="=Summary!R3C2:R3C" & pcol + 1
ActiveWorkbook.Names.Add Name:="cTrend", RefersToR1C1:= _
"=Summary!R4C2:R4C" & pcol
ActiveWorkbook.Names.Add Name:="cDate", RefersToR1C1:= _
"=Corr!R1C2:R1C" & pcol

pcol = ActiveSheet.Cells(1, 256).End(xlToLeft).Column

Sheets("Summary").Cells(5, 1).FormulaR1C1 = "=SLOPE(R3C" & pcol - 11 &
":R3C" & pcol _
& ",R1C" & pcol - 11 & ":R1C" & pcol & ")"
Sheets("Summary").Cells(6, 1).FormulaR1C1 = "=Intercept(R3C" & pcol - 11 &
":R3C" & pcol _
& ",R1C" & pcol - 11 & ":R1C" & pcol & ")"

Application.ScreenUpdating = True
Application.EnableEvents = True

End If
End If
End Sub

"jaf" wrote in message
...
Hi Steve,
Post the entire routine.


--

John

johnf202 at hotmail dot com


"steve" wrote in message
...
A real puzzler:

Dim pcol as Long

pcol = Target.Column

Target.Column shows as 16 (this is correct)
pcol shows as 17 (how can this be?)

This is embedded in a standard
Worksheet_Change(ByVal Target As Excel.Range) event macro
Excel 97 SR2, Windows 2000

Does anyone have any idea why this isn't working right?

All help is appreciated...

steve