Kalau ingin data yang terfilter di kopi di sheet lain , gunakan macro ini :
Sub Mc_AdvFilter_3()
' UNTUK MENCEGAH WORKSHEET YG TIDAK SEHARUSNYA
If UCase(ActiveSheet.Name) <> "JUMLAH" Then Exit Sub
ActiveCell.CurrentRegion.ClearContents
Dim posisiakhir As Range
Set posisiakhir = ActiveCell
Dim daerahfilter As Range
Sheets("Filternya").Activate
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set daerahfilter = Selection
Sheets("Data").Activate
Range("B2").Select
Selection.AutoFilter
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select Selection.AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
daerahfilter, Unique:=False
Selection.Copy
posisiakhir.Worksheet.Activate
posisiakhir.Select
ActiveCell.PasteSpecial
ActiveCell.CurrentRegion.Select
jumbaris = Selection.Rows.Count
jumkolom = Selection.Columns.Count
Selection.Offset(jumbaris, jumkolom - 1).Resize(1, 1).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & jumbaris - 1 & "]C:R[-1]C)"
End Sub
To: [email protected]
From: [email protected]
Date: Tue, 13 Mar 2012 07:06:29 +0000
Subject: RE: [belajar-excel] Pengambilan data auto filter dengan macro
Sub Mc_AdvFilter_2()
' UNTUK MENCEGAH WORKSHEET YG TIDAK SEHARUSNYA
If UCase(ActiveSheet.Name) <> "JUMLAH" Then Exit Sub
Dim posisiakhir As Range
Set posisiakhir = ActiveCell
Dim daerahfilter As Range
Sheets("Filternya").Activate
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set daerahfilter = Selection
Sheets("Data").Activate
Range("B2").Select
Selection.AutoFilter
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
daerahfilter, Unique:=False
Dim daerahJumlah As Range
Set daerahJumlah = Selection
Dim jumbaris, jumkolom As Long
jumbaris = Selection.Rows.Count
jumkolom = Selection.Columns.Count
Dim strrumus As String
strrumus = "=SUBTOTAL(9," & "Data!" & daerahJumlah.Address & ")"
posisiakhir.Formula = strrumus
End Sub
@jkssbma
Bagaimana caranya agar nilai subtotal dimunculkan di tempat cell dimana saya
mengeksekusi macro tersebut?
Maksudnya, saya sudah memiliki tabel dengan filter dan saya membuat tabel
lainnya sebagai summary dimana di dalam tabel tersebut akan muncul nilai2
sesuai dengan macro yang saya jalankan.
Salam
To: [email protected]
From: [email protected]
Date: Tue, 13 Mar 2012 04:58:37 +0000
Subject: RE: [belajar-excel] Pengambilan data auto filter dengan macro
Jika ingin lebih fleksibel , maka tambahkan sheet 'Filternya'
di B2 :
JENIS NAMA
HARGA
*JUS*
>10000
yang memiliki maksud :
temukan kata `JUS` di jenis menu , dengan harga > 10000 ( sepuluh ribu )
maka macronya :
Sub Mc_AdvFilter()
Dim daerahfilter As Range
Sheets("Filternya").Activate
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set daerahfilter = Selection
Sheets("Data").Activate
Range("B2").Select
Selection.AutoFilter
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
daerahfilter, Unique:=False
Dim jumbaris, jumkolom As Long
jumbaris = Selection.Rows.Count
jumkolom = Selection.Columns.Count
Dim posisiakhir As Range
Set posisiakhir = Selection.Offset(jumbaris, jumkolom - 1).Resize(1, 1)
posisiakhir.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & jumbaris - 1 & "]C:R[-1]C)"
End Sub
To: [email protected]
From: [email protected]
Date: Tue, 13 Mar 2012 03:54:35 +0000
Subject: RE: [belajar-excel] Pengambilan data auto filter dengan macro
JENIS NAMA
HARGA
MINUMAN JUS JERUK
10,000
MINUMAN JUS JAMBU
12,000
MAKANAN SOTO AYAM
10,000
MINUMAN ES TEH
5,000
BUAH STRAWBERRY
25,000
MAKANAN BAKSO
10,000
Sub mc_Filternya()
Range("B2").Select
Selection.AutoFilter
Selection.CurrentRegion.Select
' atau gunakan cara ini
' Range(Selection, Selection.End(xlToRight)).Select ' Kolom terakhir
' Range(Selection, Selection.End(xlDown)).Select ' Baris terakhir
'-------
Selection.AutoFilter Field:=1, Criteria1:="=*MAKANAN*" _
, Operator:=xlAnd
jumbaris = Selection.Rows.Count
jumkolom = Selection.Columns.Count
Dim posisiakhir as range
Set posisiakhir = Selection.Offset(jumbaris, jumkolom - 1).Resize(1, 1)
posisiakhir.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & jumbaris & "]C:R[-1]C)"
End Sub
To: [email protected]
From: [email protected]
Date: Tue, 13 Mar 2012 02:48:41 +0000
Subject: [belajar-excel] Pengambilan data auto filter dengan macro
Dear Milis,
Mohon petunjuk bagaimana perintah macro untuk mengambil data suatu field di
auto filter misalnya dengan data sederhana sbb:
JENIS NAMA HARGA
MINUMAN JUS JERUK 10000
MINUMAN JUS JAMBU 12000
MINUMAN ES TEH 5000
MAKANAN SOTO AYAM 10000
MAKANAN BAKSO 10000
Bila data tsb di auto filter bagaimana perintah dalam macro untuk menjumlahkan
semua nilai total yang ada dalam kategori MINUMAN saja?
Terima kasih sebelumnya
