Split to files then to Outlook Macro

Hi Jonathan

The Code in the attached has been modified to add Subtotals to Columns H and J of the Target Worksheets.
Code: 
.Columns("H:H").Select
                  On Error Resume Next
                  LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                                        SearchDirection:=xlPrevious, _
                                        LookIn:=xlFormulas).Row
                  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
                      SpecialCells(xlCellTypeConstants).Areas
                     Area.Offset(0, 0).Select
                     With Selection.Areas(Selection.Areas.Count)
                        x = .Cells(1).Offset(0, 0).Address
                        y = .Cells(.Cells.Count).Address
                        z = .Cells(.Cells.Count).Offset(1, 0).Address
                        Range(z).Formula = "=sum(" & x & ":" & y & ")"
                        Range(z).Font.FontStyle = "Bold"
                     End With
                  Next
                  On Error GoTo 0
                  .Columns("J:J").Select
                  On Error Resume Next
                  LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
                                        SearchDirection:=xlPrevious, _
                                        LookIn:=xlFormulas).Row
                  For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _
                      SpecialCells(xlCellTypeConstants).Areas
                     Area.Offset(0, 0).Select
                     With Selection.Areas(Selection.Areas.Count)
                        x = .Cells(1).Offset(0, 0).Address
                        y = .Cells(.Cells.Count).Address
                        z = .Cells(.Cells.Count).Offset(1, 0).Address
                        Range(z).Formula = "=sum(" & x & ":" & y & ")"
                        Range(z).Font.FontStyle = "Bold"
                     End With
                  Next
                  On Error GoTo
I can't for the life of me understand why you made this change in the Code...I believe you should change it back. You are filtering on Line 14, the Header Row is Line 9.
Code: 
If Not .AutoFilterMode Then
         .Rows("14:14").AutoFilter
         '.Rows("9:9").AutoFilter
      End If
 

Attachments

  • SplitFiles then e-mail v2.xlsm
    69.2 KB · Views: 8
Last edited:
Hi Jaslake,

I tested the macro and it work perfectly! Thank so much agian for being so helpful.:)

as for the below code, when run this the first time i am thinking of something different see below for my previous reply message #6, yes you're right i shouldn't changed this, i revert it to line 9 again.

If Not .AutoFilterMode Then
.Rows("9:9").AutoFilter
- changed to .Rows("14:14").AutoFilter - i want the macro get the filtered list on this column only and not coming from column C (vendnumname) then copy it the vendor name

also thank you for informing me about the below code that c is being used elsewhere in the Code, that's explain why it stop to "strTo".

Set c = Range("H2").End(xlDown).Offset(1, 0)

Thank you so much,
Jonathan
 
You must log in or register to reply here.
Back
Top