Simon,

As long as the column A always has data if any other cells in the row have
data, try substituting:

In line 4 of your code: ActiveSheet.UsedRange.Rows.Count < 1500 for
"UsedRange.Row < Range("A1500")"

and

in line 6 of your code: ActiveSheet.UsedRange.Rows.Count = 1500 for
"UsedRange.Row = Range("A1500")"

David

"Simon Lloyd"
wrote in message
...

Hi all, i am having trouble with the code below, i am trying to get the
event to run if the UsedRange is = Row 1500, i have tried just 1500
instead of Range("A1500").............it seems that UsedRange.Row is
always showing 1 even thought there are 2000 lines of data, i am only
interested in triggering the event if the UsedRange is =
A1500..............Any ideas?

Regards,
Simon

Private Sub Worksheet_Activate()
Dim OriginalSheet
OriginalSheet = ActiveSheet.Name
If UsedRange.Row < Range("A1500") Then
Exit Sub
ElseIf UsedRange.Row = Range("A1500") Then
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Range("A1:I1000").Select
Selection.Copy
Sheets("Summary Sheet").Select
Sheets.Add
ActiveSheet.Paste
Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveWindow.DisplayGridlines = False
ActiveSheet.Select
ActiveSheet.Tab.ColorIndex = 40
ActiveSheet.Name = "Summary Sheet" & " " & Date
Sheets(OriginalSheet).Select
Application.CutCopyMode = False
Range("A2:I1000").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Protect
End If
Application.ScreenUpdating = True
Call SvSum
End Sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=561299