Hi,

I have a problem with my macro. A msgbox pops up every
time the macro is executed. I have to manually click ok.
does anyone no how to make the macro to do this task?

What is the message box? What does it say?

Did you write the macro?

--
Regards,
Tom Ogilvy

Carl wrote in message
...
Hi,

I have a problem with my macro. A msgbox pops up every
time the macro is executed. I have to manually click ok.
does anyone no how to make the macro to do this task?

Carl's reply to my original question neither addressed my question o
even had anything to do with my original problem so I really don't kno
why it even got posted in this thread. Since Mr. Ogilvy responded t
Carl's problem in this thread, I'm concerned that my original proble
using Auto Filter in a macro will be overlooked. I don't want t
detract anybody from helping Carl out with his issue but it's probabl
better suited for another thread.

Again, if anyone has any suggestions relating to my original post
would really appreciate it.

Thanks,

Eri

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Here is some code to get you started:

First, select any cell in your dataset. Then run a macro with th
following code.

It probably would be best to NAME your dataset and replace "Selection
with Range("MyName").
Depending on how your dataset may grow you may want to redefine th
Range D3:D27


Selection.AutoFilter Field:=14, Criteria1:="A"
Range("D3:D27").Copy Sheets("Sheet2").Range("B4")
Selection.AutoFilter 'this turns Autofilter of

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

This requires your unique team names (from column P in sheet1) to be in
sheet2, Cell B3 and extending to the right with no blanks. It uses that
list to set the filter criteria for each team in that list and transfer the
ranks. It is also written to see you sheet1 data starting in column P, row
2 as you currently show.
Sorry, but recorded code would not be appropriate.


Sub DoRanks()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim rng4 As Range, cell As Range
Dim rng5 As Range
With Worksheets("Sheet2")
Set rng = .Range(.Range("B3"), _
.Range("B3").End(xlToRight))
' clears existing data below team names in
' sheet2
rng.CurrentRegion.Offset(1, 0).ClearContents
End With
With Worksheets("Sheet1")
If Not .AutoFilterMode Then
Set rng1 = .Range(.Range("P2"), _
.Range("P2").End(xlDown))
rng1.AutoFilter
End If
Set rng2 = .AutoFilter.Range
Set rng3 = rng2.Offset(1, 0).Resize(rng2.Rows.Count - 1, 1)
Set rng4 = rng3.Offset(0, -12)
For Each cell In rng
rng2.AutoFilter Field:=1, Criteria1:="=" & cell.Value
Set rng5 = rng2.SpecialCells(xlVisible)
If rng5.Count 1 Then
rng4.Copy Destination:=cell.Offset(1, 0)
End If
Next
.ShowAllData
End With

End Sub


--
Regards,
Tom Ogilvy




hawkeyes2002 wrote in message
...

Carl's reply to my original question neither addressed my question or
even had anything to do with my original problem so I really don't know
why it even got posted in this thread. Since Mr. Ogilvy responded to
Carl's problem in this thread, I'm concerned that my original problem
using Auto Filter in a macro will be overlooked. I don't want to
detract anybody from helping Carl out with his issue but it's probably
better suited for another thread.

Again, if anyone has any suggestions relating to my original post I
would really appreciate it.

Thanks,

Eric


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

"Carl" wrote in message ...
Hi,

I have a problem with my macro. A msgbox pops up every
time the macro is executed. I have to manually click ok.
does anyone no how to make the macro to do this task?


Carl,

Did you ever find this answer. I'm looking for it too. In my case
the msgboxes are self created. I have one at the end of several
macros. When I write a macro that will call and run each of the
individual ones, I have to click "OK" about 20 times. Let me know if
anyone can help to automate this.

thanks