Hi all, i am having trouble with the code below, i am trying to get th
event to run if the UsedRange is = Row 1500, i have tried just 150
instead of Range("A1500").............it seems that UsedRange.Row i
always showing 1 even thought there are 2000 lines of data, i am onl
interested in triggering the event if the UsedRange is
A1500..............Any ideas?, I am only interested in triggering th
event if range("A1:A1500") is full the event must not trigger if an
other column is filled up to or past this point, i hav
=VLOOKUP(F2,EventList,2,FALSE) and others in column "I" down to Ro
3000 this is the reason i only wanted to trigger the event if the dat
existed in column A =1500.

One other problem i have is where i delete cells between A2:H1000 an
move cells up the formulae i have in column "I" shows #REF! where it n
longer references the cells i set i.e F2 how can i cure this?


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 Su

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=56355

UsedRange.Row < Range("A1500")

The row property returns the first row number of the specified range.
UsedRange.Rows.Count would give you the last row number. Range("A1500")
refers to value of that cell (value is the default property of range objects).

Since you want the last row of Col A, I would suggest
Cells(Rows.Count, 1).End(xlup).Row < 1500

When rows are deleted, you'll get errors in dependent formulae. If you want
your formulae to refer to the new value that is in F2, try INDIRECT("F2").
Otherwise, all I could suggest is don't delete the cells (or clear the cells
and hide them by setting the hidden property to true).


"Simon Lloyd" wrote:


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?, I am only interested in triggering the
event if range("A1:A1500") is full the event must not trigger if any
other column is filled up to or past this point, i have
=VLOOKUP(F2,EventList,2,FALSE) and others in column "I" down to Row
3000 this is the reason i only wanted to trigger the event if the data
existed in column A =1500.

One other problem i have is where i delete cells between A2:H1000 and
move cells up the formulae i have in column "I" shows #REF! where it no
longer references the cells i set i.e F2 how can i cure this?


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=563555