Macro

  • Thread starter Thread starter Carl
  • Start date Start date
C

Carl

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
 
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
 
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
 
Carl said:
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?
Click to expand...


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
 
Back
Top