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 Removed)
Replace @mailinator.com with @tiscali.co.uk
"SGT Buckeye" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
>
Similar Threads
