VB select code for a worksheet
 

I would like to dissect one worksheet that has all my raw data into filtered
selects in indivdual worksheets. For instance...Worksheet 1 has all my data
and now I want worksheet 2 to filter records from worksheet 1.

Let's say I have codes CT, MR, US in worksheet 1 and each code has detail
data assigned to them. I want worksheet 2 to only include 'CT' and the
detail data and so on for the other codes. How can I write code that will
refresh each worksheet throughout the workbook based on my data in worksheet
1?

VB select code for a worksheet
 

Have you condidered using a pivot table to display your data in a seperate
sheet. You can also use the show pages feature of the pivot to create sperate
sheets for each grouping of data.
--
HTH...

Jim Thomlinson


"Vicki" wrote:

I would like to dissect one worksheet that has all my raw data into filtered
selects in indivdual worksheets. For instance...Worksheet 1 has all my data
and now I want worksheet 2 to filter records from worksheet 1.

Let's say I have codes CT, MR, US in worksheet 1 and each code has detail
data assigned to them. I want worksheet 2 to only include 'CT' and the
detail data and so on for the other codes. How can I write code that will
refresh each worksheet throughout the workbook based on my data in worksheet
1?

VB select code for a worksheet
 

I have never used the show pages feature. Could you explain a little
further. This probably would be my best option. Thank you.

"Jim Thomlinson" wrote:

Have you condidered using a pivot table to display your data in a seperate
sheet. You can also use the show pages feature of the pivot to create sperate
sheets for each grouping of data.
--
HTH...

Jim Thomlinson


"Vicki" wrote:

I would like to dissect one worksheet that has all my raw data into filtered
selects in indivdual worksheets. For instance...Worksheet 1 has all my data
and now I want worksheet 2 to filter records from worksheet 1.

Let's say I have codes CT, MR, US in worksheet 1 and each code has detail
data assigned to them. I want worksheet 2 to only include 'CT' and the
detail data and so on for the other codes. How can I write code that will
refresh each worksheet throughout the workbook based on my data in worksheet
1?

VB select code for a worksheet
 

what if I were to use this code (and where would I put the Column Auto.Fit
code as each sheet is created?):
Sub ExtractCodes()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("AllCrosswalkCodes")
Set rng = Range("CodesDatabase")

'extract a list of Modality Codes
ws1.Columns("A:A").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("A1").Value

For Each c In Range("J2:J" & r)
'add the modality codes to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("AllCrosswalkCodes").Range(" L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False

Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("AllCrosswalkCodes").Range(" L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False



End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function

"Jim Thomlinson" wrote:

Have you condidered using a pivot table to display your data in a seperate
sheet. You can also use the show pages feature of the pivot to create sperate
sheets for each grouping of data.
--
HTH...

Jim Thomlinson


"Vicki" wrote:

I would like to dissect one worksheet that has all my raw data into filtered
selects in indivdual worksheets. For instance...Worksheet 1 has all my data
and now I want worksheet 2 to filter records from worksheet 1.

Let's say I have codes CT, MR, US in worksheet 1 and each code has detail
data assigned to them. I want worksheet 2 to only include 'CT' and the
detail data and so on for the other codes. How can I write code that will
refresh each worksheet throughout the workbook based on my data in worksheet
1?