advance filter question

[SGT Buckeye]

I have a master worksheet with 7 columns of data. In the second colum
of data, there will be five possible entries:1, 2, 3, OPS, HQ. I
would like to pull all data associated with each possible entry to a
seperate to a page of its own. I am familiar with advanced filter so I
can pull the data for one of the possible entries. But when I change
the criteria to pull the second set of data, I lose the filter for the
first set of data. See below:

Name Platoon Badge Certificate Score Go/No-Go Date
Saunders 1 YES BRONZE 280 GO 9/28/2007
Morris 2 NO NONE 255 GO 9/28/2007
Arsene 3 NO NONE 254 GO 9/28/2007
Gonzalez HQ NO NONE 280 GO 9/28/2007
Robles OPS NO NONE 224 GO 9/28/2007
Feliciano 1 NO NONE 207 NO-GO 9/28/2007
Maisonave 2 NO NONE 187 NO-GO 9/28/2007
Velez 3 NO NONE 214 GO 9/28/2007
Castro HQ NO NONE 253 GO 9/28/2007

 

[Farhad]

Hi,

My sugestion is to use advanced filter and copy the filtered data in another
sheet well you can do it for each set of filtered data to a separate sheet
though.

Thanks,

 

[Sandy Mann]

Try this Macro to copy to the required sheets:

Option Explicit
Sub CopyIt()

Dim Platoon(1 To 5) As String
Dim sPlatoon(1 To 5) As String
Dim x As Integer
Dim LRow As Long

sPlatoon(1) = "One"
'Change to the name of your Platoon 1 sheet name
sPlatoon(2) = "Two"
sPlatoon(3) = "Three"
sPlatoon(4) = "HQ"
sPlatoon(5) = "OPS"

Platoon(1) = 1
Platoon(2) = 2
Platoon(3) = 3
Platoon(4) = "HQ"
Platoon(5) = "OPS"

Application.ScreenUpdating = False

With Sheets("Sheet2")
'Change to the name of your master sheet

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For x = 1 To 5

.Range(Cells(1, 1), Cells(LRow, 7)) _
.AutoFilter Field:=2, Criteria1:=Platoon(x)

.Range(Cells(1, 1), Cells(LRow, 7)) _
.Copy Destination:=Sheets(sPlatoon(x)).Range("A1")
Next x
.Range(Cells(1, 1), Cells(LRow, 7)).AutoFilter
End With

Application.ScreenUpdating = True
End Sub

This assumes that the data starts in A1.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[SGT Buckeye]

Try this Macro to copy to the required sheets:

Option Explicit
Sub CopyIt()

Dim Platoon(1 To 5) As String
Dim sPlatoon(1 To 5) As String
Dim x As Integer
Dim LRow As Long

sPlatoon(1) = "One"
'Change to the name of your Platoon 1 sheet name
sPlatoon(2) = "Two"
sPlatoon(3) = "Three"
sPlatoon(4) = "HQ"
sPlatoon(5) = "OPS"

Platoon(1) = 1
Platoon(2) = 2
Platoon(3) = 3
Platoon(4) = "HQ"
Platoon(5) = "OPS"

Application.ScreenUpdating = False

With Sheets("Sheet2")
'Change to the name of your master sheet

LRow = .Cells(.Rows.Count, 1).End(xlUp).Row

For x = 1 To 5

.Range(Cells(1, 1), Cells(LRow, 7)) _
.AutoFilter Field:=2, Criteria1:=Platoon(x)

.Range(Cells(1, 1), Cells(LRow, 7)) _
.Copy Destination:=Sheets(sPlatoon(x)).Range("A1")
Next x
.Range(Cells(1, 1), Cells(LRow, 7)).AutoFilter
End With

Application.ScreenUpdating = True
End Sub

This assumes that the data starts in A1.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk







- Show quoted text -

Thanks for all the suggestions but I figured out something that works
for me. I copied all the data from the master worksheet to each of
the platoon worksheets. I then used the Record Macro feature of Word
2007 to unprotect the sheets, filter by platoon on each sheet, sort
from high score to low score, and then protect the sheets again. It
works like a charm. Again, many thanks for the suggestions.

 

[Sandy Mann]

As long as you have a solutuion that works for you, that is what is
important.

Thanks for posting back.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk