Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
select Range Code | Excel Programming | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
Code to Select Row | Excel Programming |