Hi all,
I hv data sheet and make another summary sheet with one criteria. I can
write code, which is show you. Problem is that its do all column in my
summary report from data sheet ( column A6 to G6) but I wants selected column
(in my summary)like A,B,D,F only. please help me what i change in my code.
and please tell me if i can increase my Criteria to 2 or 3 then what we
change.

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:g6"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("Data Entry").Range("D2").Calculate
End If
End Sub

Thanks in advance
RKS

Try using an inputbox. You can select multiple columns by holding down the
CNTRL key.

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").Selec t
Set myRange = Application.InputBox(prompt:="Select Columns", Type:=8)

myRange.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("ProductsList").Range("Crite ria"), _
CopyToRange:=Range("A6:g6"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("Data Entry").Range("D2").Calculate
End If
End Sub

"RKS" wrote:

Hi all,
I hv data sheet and make another summary sheet with one criteria. I can
write code, which is show you. Problem is that its do all column in my
summary report from data sheet ( column A6 to G6) but I wants selected column
(in my summary)like A,B,D,F only. please help me what i change in my code.
and please tell me if i can increase my Criteria to 2 or 3 then what we
change.

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:g6"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("Data Entry").Range("D2").Calculate
End If
End Sub

Thanks in advance
RKS