I am not familiar with macros at all so I am just following the advice I have
already been given. If you can explain what I should do I will certainly try
it. Thanks.

"Gord Dibben" wrote:

Why don't you store the macro in a General module where it should be instead
of in a sheet module which are more commonly used for event type code?


Gord Dibben MS Excel MVP

On Fri, 25 Sep 2009 12:51:02 -0700, Jambruins
wrote:

I get a type mismatch error when I run it. Any suggestions? The sheet is
called CALCS and I would like it to paste into a sheet called PLAYS in cell
N1. I tried changing the Sheet 2 reference to PLAYS but I got the same
error. Thanks.

"Mike H" wrote:

Hi,

Right click the sheet tab that contains your data, View code and paste the
code in on the right. To run it Tap F5 while in VB editor. Or tools macro
from the worksheet

Mike

"Jambruins" wrote:

how do I put it in worksheet code? Thanks.

"Mike H" wrote:

Hi,

I don't think filtering and copying is that onerous but if you want a macto
here's one.
Put it in as worksheet code in the sheet with your data and the relevent
rows will be copied to sheet 2

Sub Please_Copy_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "G").End(xlUp).Row
Set MyRange = Range("G3:G" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "PLAY" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End Sub

Mike

"Jambruins" wrote:

I can do this but the sheet is going to be updated every week and I was
hoping to have something automated so I wouldn't have to do this every week.
This will work though if nobody knows a formula to do this. Thanks.

"Mike H" wrote:

Hi,

Apply a filter and sort the data Z to A which will put the blanks at the
bottom and copy and paste the top rows.

Mike

"Jambruins" wrote:

In column A I have a bunch of team names. I column G there is either the
word "PLAY" or it is empty. On a seperate sheet I would like to have all the
teams with the word "PLAY" in the corresponding G cell to be listed. For
example:

Cell A3 has Missouri in it but cell G3 is empty
Cell A5 has Wake Forest in it and cell G5 has "PLAY" in it.
Cell A7 has Illinois in it but cell G7 is empty
Cell A9 has Minnesota in it and cell G9 has "PLAY" in it.

I would like my new sheet to show
Wake Forest
Minnesota

I don't want the sheet to have empty rows like

Wake Forest

Minnesota


Thanks for the help.