Auto sort and email

[sknight22]

Hello

I have a large database of orders that need to be sent out to various email addresses and wondered; if one column contains company numbers, is it possible for a macro to filter the data into separate tabs (grouped by the company) and email out to specified addresses?

I’ve seen something similar but can’t find it

I hope you can help

Thanks

Sknight22

 

[Fluff]

This will do the first, but not the e-mail.

Sub FilterCopy()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("[COLOR=#ff0000]Pcode[/COLOR]")
Application.ScreenUpdating = False
   If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("[COLOR=#ff0000]A2[/COLOR]", Ws.Range("[COLOR=#ff0000]A[/COLOR]" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
            .Add Cl.Value, Nothing
            Ws.Range("[COLOR=#ff0000]A1:U1[/COLOR]").AutoFilter 1, Cl.Value
            Ws.AutoFilter.Range.SpecialCells(xlVisible).Copy Sheets(Cl.Value).Range("A1")
         End If
      Next Cl
   End With
   Ws.AutoFilterMode = False
End Sub

Change the sheet name to suit.
Its based on your company names being in col A with a header in row 1 cols A:U. Change values in red to suit

 

[sknight22]

Hello,

Thank you for your response.

This works great but it flags an error with "Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value" highlighted, but not sure why.

Also, if the company names were in Column H what would I need to change?

Thanks again for your help

Stephen

 

[Fluff]

To look in col H try

Sub FilterCopy()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("[COLOR=#ff0000]Pcode[/COLOR]")
Application.ScreenUpdating = False
   If Ws.AutoFilterMode Then Ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws.Range("[COLOR=#ff0000]H2[/COLOR]", Ws.Range("[COLOR=#ff0000]H[/COLOR]" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            Sheets.Add(, Sheets(Sheets.Count)).Name = Cl.Value
            .Add Cl.Value, Nothing
            Ws.Range("A1:U1").AutoFilter [COLOR=#ff0000]8[/COLOR], Cl.Value
            Ws.AutoFilter.Range.SpecialCells(xlVisible).Copy Sheets(Cl.Value).Range("A1")
         End If
      Next Cl
   End With
   Ws.AutoFilterMode = False
End Sub

Are they Company names or numbers?

 

[sknight22]

Sorry they are company names. I’m not sure why I typed numbers in the original post.
Thanks I will try that