Hi,
I want to count and sum of the autofilter data in sheet1 to sheet2
below is the code with erros msg -
sub addautofitler()
Dim Sh As Worksheet
Dim Rng As Range
Dim ws As Worksheet
Dim Cell As Range
Dim Unique As New Collection
Dim i As Integer
Dim rcount As Long
Dim esum As Long
Dim ShTarget As Worksheet
Set Sh = Worksheets("Data")
With Sh
Set Rng = .Range("G2:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
End With
For Each Cell In Rng
Unique.Add Cell.Value, CStr(Cell.Value) '// Key is already associated with an element of this collection
Next Cell
On Error GoTo 0
Set Rng = Sh.Range("A1").CurrentRegion
For i = 1 To Rng
Rng.AutoFilter
Rng.AutoFilter Field:=7, Criteria1:=Unique(i)
Set ShTarget = Worksheets("tables")
rcount = Rng.SpecialCells(xlCellTypeVisible).Count
ShTarget.Range(a2).Value = rcount
'//want to add value of J column and paste it in A3
Next i
With Sh
.AutoFilterMode = False
.Select
End With
End Sub
I want to count and sum of the autofilter data in sheet1 to sheet2
below is the code with erros msg -
sub addautofitler()
Dim Sh As Worksheet
Dim Rng As Range
Dim ws As Worksheet
Dim Cell As Range
Dim Unique As New Collection
Dim i As Integer
Dim rcount As Long
Dim esum As Long
Dim ShTarget As Worksheet
Set Sh = Worksheets("Data")
With Sh
Set Rng = .Range("G2:G" & .Range("G" & .Rows.Count).End(xlUp).Row)
End With
For Each Cell In Rng
Unique.Add Cell.Value, CStr(Cell.Value) '// Key is already associated with an element of this collection
Next Cell
On Error GoTo 0
Set Rng = Sh.Range("A1").CurrentRegion
For i = 1 To Rng
Rng.AutoFilter
Rng.AutoFilter Field:=7, Criteria1:=Unique(i)
Set ShTarget = Worksheets("tables")
rcount = Rng.SpecialCells(xlCellTypeVisible).Count
ShTarget.Range(a2).Value = rcount
'//want to add value of J column and paste it in A3
Next i
With Sh
.AutoFilterMode = False
.Select
End With
End Sub