Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance Filter
Hi All,
PLease help me. I Have a Data sheet which have 200 record which we protect and deliverd to user and have A to I column. I can another Product List where data comes from datasheet with one critera. I can take help from http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - which is 95% same as my requirement. I have only one problem. in my data sheet have A to I column and In product sheet I need only selected column like A,B,D,F,G only. I can show which code i use. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calculate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub Exmaple Data Sheet Product sheet (after advance filter) Column A B C D E F G H I A B D F G Thanks RKS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance Filter
As answered in microsoft.public.excel.misc:
Instead of including all the column headings in the extract area, just display the headings for the A, C and F columns. Then, change the code so it refers to those headings, e.g. CopyToRange:=Range("A6:C6"), Unique:=False RKS wrote: Hi All, PLease help me. I Have a Data sheet which have 200 record which we protect and deliverd to user and have A to I column. I can another Product List where data comes from datasheet with one critera. I can take help from http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - which is 95% same as my requirement. I have only one problem. in my data sheet have A to I column and In product sheet I need only selected column like A,B,D,F,G only. I can show which code i use. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calculate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub Exmaple Data Sheet Product sheet (after advance filter) Column A B C D E F G H I A B D F G Thanks RKS -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance Filter
Hi Debra
Please change my code so i can understand what change need. I need only A,C and F column. i can try many time but not success. so please change so i can copy only selected column. Thanks Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calculate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub If u not notunderstand please see http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - example. Thanks RKS "Debra Dalgleish" wrote: As answered in microsoft.public.excel.misc: Instead of including all the column headings in the extract area, just display the headings for the A, C and F columns. Then, change the code so it refers to those headings, e.g. CopyToRange:=Range("A6:C6"), Unique:=False RKS wrote: Hi All, PLease help me. I Have a Data sheet which have 200 record which we protect and deliverd to user and have A to I column. I can another Product List where data comes from datasheet with one critera. I can take help from http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - which is 95% same as my requirement. I have only one problem. in my data sheet have A to I column and In product sheet I need only selected column like A,B,D,F,G only. I can show which code i use. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calculate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub Exmaple Data Sheet Product sheet (after advance filter) Column A B C D E F G H I A B D F G Thanks RKS -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance Filter
First, you have to set up the Extract range with the column headings for
A, C and F. Also, set up and name the criteria range. There are instructions he http://www.contextures.com/xladvfilter01.html Then, change the sheet names in the code, to match the sheet names in your workbook. In the sample file, the text is entered in cell C2 (row 2, column 3) If your entry cell is different, change the target row and target column in the code. RKS wrote: Hi Debra Please change my code so i can understand what change need. I need only A,C and F column. i can try many time but not success. so please change so i can copy only selected column. Thanks Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calculate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub If u not notunderstand please see http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - example. Thanks RKS "Debra Dalgleish" wrote: As answered in microsoft.public.excel.misc: Instead of including all the column headings in the extract area, just display the headings for the A, C and F columns. Then, change the code so it refers to those headings, e.g. CopyToRange:=Range("A6:C6"), Unique:=False RKS wrote: Hi All, PLease help me. I Have a Data sheet which have 200 record which we protect and deliverd to user and have A to I column. I can another Product List where data comes from datasheet with one critera. I can take help from http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - which is 95% same as my requirement. I have only one problem. in my data sheet have A to I column and In product sheet I need only selected column like A,B,D,F,G only. I can show which code i use. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calcul ate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub Exmaple Data Sheet Product sheet (after advance filter) Column A B C D E F G H I A B D F G Thanks RKS -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance Filter
Hi Debra
Thanks for reply. but can't understand what u say. please show me after change in example. so i can filter only 3 column like A,C and F. please show me criteria, Extract etc. after change which u say me. my purpose only select only particular column which i wants not all column range. waiting your reply, "Debra Dalgleish" wrote: First, you have to set up the Extract range with the column headings for A, C and F. Also, set up and name the criteria range. There are instructions he http://www.contextures.com/xladvfilter01.html Then, change the sheet names in the code, to match the sheet names in your workbook. In the sample file, the text is entered in cell C2 (row 2, column 3) If your entry cell is different, change the target row and target column in the code. RKS wrote: Hi Debra Please change my code so i can understand what change need. I need only A,C and F column. i can try many time but not success. so please change so i can copy only selected column. Thanks Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calculate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub If u not notunderstand please see http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - example. Thanks RKS "Debra Dalgleish" wrote: As answered in microsoft.public.excel.misc: Instead of including all the column headings in the extract area, just display the headings for the A, C and F columns. Then, change the code so it refers to those headings, e.g. CopyToRange:=Range("A6:C6"), Unique:=False RKS wrote: Hi All, PLease help me. I Have a Data sheet which have 200 record which we protect and deliverd to user and have A to I column. I can another Product List where data comes from datasheet with one critera. I can take help from http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - which is 95% same as my requirement. I have only one problem. in my data sheet have A to I column and In product sheet I need only selected column like A,B,D,F,G only. I can show which code i use. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calcul ate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub Exmaple Data Sheet Product sheet (after advance filter) Column A B C D E F G H I A B D F G Thanks RKS -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advance Filter
You have to set up your worksheet before you change the code. As I
suggested earlier, read the information on Advanced Filters he http://www.contextures.com/xladvfilter01.html It shows how to set up the extract range with only some of the headings from the product table. In my first reply, I said to change the CopyTo reference in the code, so it's only 3 cells: CopyToRange:=Range("A6:C6"), Unique:=False RKS wrote: Hi Debra Thanks for reply. but can't understand what u say. please show me after change in example. so i can filter only 3 column like A,C and F. please show me criteria, Extract etc. after change which u say me. my purpose only select only particular column which i wants not all column range. waiting your reply, "Debra Dalgleish" wrote: First, you have to set up the Extract range with the column headings for A, C and F. Also, set up and name the criteria range. There are instructions he http://www.contextures.com/xladvfilter01.html Then, change the sheet names in the code, to match the sheet names in your workbook. In the sample file, the text is entered in cell C2 (row 2, column 3) If your entry cell is different, change the target row and target column in the code. RKS wrote: Hi Debra Please change my code so i can understand what change need. I need only A,C and F column. i can try many time but not success. so please change so i can copy only selected column. Thanks Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calculate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub If u not notunderstand please see http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - example. Thanks RKS "Debra Dalgleish" wrote: As answered in microsoft.public.excel.misc: Instead of including all the column headings in the extract area, just display the headings for the A, C and F columns. Then, change the code so it refers to those headings, e.g. CopyToRange:=Range("A6:C6"), Unique:=False RKS wrote: Hi All, PLease help me. I Have a Data sheet which have 200 record which we protect and deliverd to user and have A to I column. I can another Product List where data comes from datasheet with one critera. I can take help from http://www.contextures.com/excelfiles.html under Filters, for 'FL0002 - which is 95% same as my requirement. I have only one problem. in my data sheet have A to I column and In product sheet I need only selected column like A,B,D,F,G only. I can show which code i use. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then 'calculate criteria cell in case calculation mode is manual Sheets("ProductsList").Range("Criteria").Calc ulate Worksheets("ProductsList").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _ CopyToRange:=Range("A6:I6"), Unique:=False 'calculate summary total in case calculation mode is manual Sheets("Data Entry").Range("D2").Calculate End If End Sub Exmaple Data Sheet Product sheet (after advance filter) Column A B C D E F G H I A B D F G Thanks RKS -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance Filter | Excel Worksheet Functions | |||
Advance filter | Excel Programming | |||
Advance filter search does not filter an exact match | Excel Programming | |||
advance filter | Excel Discussion (Misc queries) | |||
Advance Filter | Excel Programming |