S
Simon Lloyd
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
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