Hi everyone,
I'm sending a macro that basically filters the unique values of column A on a worksheet and creates individual sheets for each group (and then sends different e-mails for each group).
Column K has currency amounts, and what I'd like to do is to add a sub-total to that column so that the e-mail recipients can see the result on the e-mail without having to do further calculations.
Is it possible to do this?
The part of the macro that filters the data is this (Excel 2007):
Thanks in advance,
Cheers
Miguel
'Set filter sheet, you can also use Sheets("MySheet")
Set Ash = ActiveSheet
'Set filter range and filter column (Column with names)
Set FilterRange = Ash.Range("A1:K" & Ash.Rows.Count)
FieldNum = 1 'Filter column = A because the filter range start in A
'Add a worksheet for the unique list and copy the unique list in A1
Set Cws = Worksheets.Add
FilterRange.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Cws.Range("A1"), _
CriteriaRange:="", Unique:=True
'Count of the unique values + the header cell
Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))
'If there are unique values start the loop
If Rcount >= 2 Then
For Rnum = 2 To Rcount
'Filter the FilterRange on the FieldNum column
FilterRange.AutoFilter Field:=FieldNum, _
Criteria1:=Cws.Cells(Rnum, 1).Value
I'm sending a macro that basically filters the unique values of column A on a worksheet and creates individual sheets for each group (and then sends different e-mails for each group).
Column K has currency amounts, and what I'd like to do is to add a sub-total to that column so that the e-mail recipients can see the result on the e-mail without having to do further calculations.
Is it possible to do this?
The part of the macro that filters the data is this (Excel 2007):
Thanks in advance,
Cheers
Miguel
'Set filter sheet, you can also use Sheets("MySheet")
Set Ash = ActiveSheet
'Set filter range and filter column (Column with names)
Set FilterRange = Ash.Range("A1:K" & Ash.Rows.Count)
FieldNum = 1 'Filter column = A because the filter range start in A
'Add a worksheet for the unique list and copy the unique list in A1
Set Cws = Worksheets.Add
FilterRange.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Cws.Range("A1"), _
CriteriaRange:="", Unique:=True
'Count of the unique values + the header cell
Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))
'If there are unique values start the loop
If Rcount >= 2 Then
For Rnum = 2 To Rcount
'Filter the FilterRange on the FieldNum column
FilterRange.AutoFilter Field:=FieldNum, _
Criteria1:=Cws.Cells(Rnum, 1).Value