You show the Worksheet_Activate event, so I assume this is in the code
module for the "CURRENT" sheet.

Private Sub Worksheet_Activate()
Dim num As String
Me.UsedRange.ClearContents
Num = 1
For i = 2 To 150

If Worksheets("MASTER LIST").Cells(i, 5) = "C" Then
Worksheets("MASTER LIST").Range("A" & _
i & ":G" & i).copy Destination:= _
Sheets("CURRENT").Range("A" & num)
num = num + 1
Else
'messagebox is only here for my testing purposes, and will be
'deleted once the macro is working
'MsgBox ("Not current")
End If
Next i
End Sub

this should update the data each time CURRENT is activated. (that is one
reason you don't want to use Select and Activate for sheets within the
code).

--
Regards,
Tom Ogilvy


"madbloke " wrote in message
...
Cheers! That works!

Couple of little issues that you might be able to help me with
(probably a bit beyond my capabilities!)

As it is now, the macro leaves blank rows for non-compliant entries. Is
there any way to get round this?

Also, I'd need it to refresh each time the Current sheet is selected.
(I.E. I'd need it to clear the sheet, and pull the info through again,
otherwise changes to the master list don't show up). Is there a simple
command that i'd put before the If statement?


Tom Ogilvy wrote:
[b]When you select the other sheet, you never come back. You also had
an error
in your address string. You have the name
Sheets("CURRENT ")
with a space on the end. I took out the space, but if there really
is a
space in the name, you need to add it back in.

Private Sub Worksheet_Activate()
Dim num As String
For i = 2 To 150
num = i
If Worksheets("MASTER LIST").Cells(i, 5) = "C" Then
Worksheets("MASTER LIST").Range("A" & _
num & ":G" & num).copy Destination:= _
Sheets("CURRENT").Range("A" & num)
Else
'messagebox is only here for my testing purposes, and will be
'deleted once the macro is working
MsgBox ("Not current")
End If
Next i
End Sub


--
Regards,
Tom Ogilvy



---
Message posted from http://www.ExcelForum.com/