I have an Excel file with 198 rows and several columns. One of the columns
is a list of the different "carriers". Out of the 198 rows there is only
10-12 different carriers. The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? Any help will be greatly apprciated.

Cindy.

Sure, check out

http://www.rondebruin.nl/copy5.htm


HTH,
JP

On Jan 28, 1:12*pm, Jordan <Jor...@discussions.microsoft.com> wrote:
> I have an Excel file with 198 rows and several columns. *One of the columns
> is a list of the different "carriers". *Out of the 198 rows there is only
> 10-12 different carriers. *The rest of the columns are misc data.
>
> I need to send this information out to each carrier but can only show each
> carrier their own information. *In otherwords; I need to sort by the column
> listing the carrieres and then divide the information up by each unique value
> in that column.
>
> Is there anyway to automate this? *Any help will be greatly apprciated.
>
> Cindy.

Hi Jordan

Is the mail addresss of each carrier also in the table ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" <(E-Mail Removed)> wrote in message news:CC36060B-6232-4BE2-BFC9-(E-Mail Removed)...
>I have an Excel file with 198 rows and several columns. One of the columns
> is a list of the different "carriers". Out of the 198 rows there is only
> 10-12 different carriers. The rest of the columns are misc data.
>
> I need to send this information out to each carrier but can only show each
> carrier their own information. In otherwords; I need to sort by the column
> listing the carrieres and then divide the information up by each unique value
> in that column.
>
> Is there anyway to automate this? Any help will be greatly apprciated.
>
> Cindy.
>
>

No. I was hoping to have each email saved as a draft or left open so the
address could be added. Thanks for your help.

"Ron de Bruin" wrote:

> Hi Jordan
>
> Is the mail addresss of each carrier also in the table ?
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Jordan" <(E-Mail Removed)> wrote in message news:CC36060B-6232-4BE2-BFC9-(E-Mail Removed)...
> >I have an Excel file with 198 rows and several columns. One of the columns
> > is a list of the different "carriers". Out of the 198 rows there is only
> > 10-12 different carriers. The rest of the columns are misc data.
> >
> > I need to send this information out to each carrier but can only show each
> > carrier their own information. In otherwords; I need to sort by the column
> > listing the carrieres and then divide the information up by each unique value
> > in that column.
> >
> > Is there anyway to automate this? Any help will be greatly apprciated.
> >
> > Cindy.
> >
> >
>

Use the code to split the data in different sheets
See reply from JP

Then loop through the sheets and mail them like this

Change the sheet name here to your data sheet with all the data
If sh.Name <> "YourDataSheet" Then


Sub Mail_Every_Worksheet()
'Working in 97-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

TempFilePath = Environ$("temp") & "\"

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ThisWorkbook.Worksheets
If sh.Name <> "YourDataSheet" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" <(E-Mail Removed)> wrote in message news:6B028D2B-460E-48B8-9B1C-(E-Mail Removed)...
> No. I was hoping to have each email saved as a draft or left open so the
> address could be added. Thanks for your help.
>
> "Ron de Bruin" wrote:
>
>> Hi Jordan
>>
>> Is the mail addresss of each carrier also in the table ?
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Jordan" <(E-Mail Removed)> wrote in message news:CC36060B-6232-4BE2-BFC9-(E-Mail Removed)...
>> >I have an Excel file with 198 rows and several columns. One of the columns
>> > is a list of the different "carriers". Out of the 198 rows there is only
>> > 10-12 different carriers. The rest of the columns are misc data.
>> >
>> > I need to send this information out to each carrier but can only show each
>> > carrier their own information. In otherwords; I need to sort by the column
>> > listing the carrieres and then divide the information up by each unique value
>> > in that column.
>> >
>> > Is there anyway to automate this? Any help will be greatly apprciated.
>> >
>> > Cindy.
>> >
>> >
>>

JP - Thanks for the lead.

Ron de Bruin. Thank you. Thank you. Thank you. I used the macro and it
does exactly what we need it to do. This is going to save many hours in a
day. I have a couple of questions, if you dont mind.
(1) Is there a way to get rid of the red cell highlighing in the created
files.
(2) I am really new to this. How do I add the second set of code to the
first.

Again, this is very helpful so many thanks.

"Ron de Bruin" wrote:

> Use the code to split the data in different sheets
> See reply from JP
>
> Then loop through the sheets and mail them like this
>
> Change the sheet name here to your data sheet with all the data
> If sh.Name <> "YourDataSheet" Then
>
>
> Sub Mail_Every_Worksheet()
> 'Working in 97-2007
> Dim sh As Worksheet
> Dim wb As Workbook
> Dim FileExtStr As String
> Dim FileFormatNum As Long
> Dim TempFilePath As String
> Dim TempFileName As String
>
> TempFilePath = Environ$("temp") & "\"
>
> If Val(Application.Version) < 12 Then
> 'You use Excel 97-2003
> FileExtStr = ".xls": FileFormatNum = -4143
> Else
> 'You use Excel 2007
> FileExtStr = ".xlsm": FileFormatNum = 52
> End If
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> "YourDataSheet" Then
>
> sh.Copy
> Set wb = ActiveWorkbook
>
> TempFileName = "Sheet " & sh.Name & " of " _
> & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
>
> With wb
> .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
> On Error Resume Next
> .SendMail "", _
> "This is the Subject line"
> On Error GoTo 0
> .Close SaveChanges:=False
> End With
>
> Kill TempFilePath & TempFileName & FileExtStr
>
> End If
> Next sh
>
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
> End Sub
>
>
>
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Jordan" <(E-Mail Removed)> wrote in message news:6B028D2B-460E-48B8-9B1C-(E-Mail Removed)...
> > No. I was hoping to have each email saved as a draft or left open so the
> > address could be added. Thanks for your help.
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi Jordan
> >>
> >> Is the mail addresss of each carrier also in the table ?
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "Jordan" <(E-Mail Removed)> wrote in message news:CC36060B-6232-4BE2-BFC9-(E-Mail Removed)...
> >> >I have an Excel file with 198 rows and several columns. One of the columns
> >> > is a list of the different "carriers". Out of the 198 rows there is only
> >> > 10-12 different carriers. The rest of the columns are misc data.
> >> >
> >> > I need to send this information out to each carrier but can only show each
> >> > carrier their own information. In otherwords; I need to sort by the column
> >> > listing the carrieres and then divide the information up by each unique value
> >> > in that column.
> >> >
> >> > Is there anyway to automate this? Any help will be greatly apprciated.
> >> >
> >> > Cindy.
> >> >
> >> >
> >>
>

>(1) Is there a way to get rid of the red cell highlighing in the created
>files.

Sorry I not understand what you mean with this

2: You can call the mail macro in the splt macro in the last line like

Call Mail_Every_Worksheet

If you use Outlook you can also use another macro that you can use to add text in the body



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" <(E-Mail Removed)> wrote in message news:90EA36D9-5348-4C45-B33F-(E-Mail Removed)...
> JP - Thanks for the lead.
>
> Ron de Bruin. Thank you. Thank you. Thank you. I used the macro and it
> does exactly what we need it to do. This is going to save many hours in a
> day. I have a couple of questions, if you dont mind.
> (1) Is there a way to get rid of the red cell highlighing in the created
> files.
> (2) I am really new to this. How do I add the second set of code to the
> first.
>
> Again, this is very helpful so many thanks.
>
> "Ron de Bruin" wrote:
>
>> Use the code to split the data in different sheets
>> See reply from JP
>>
>> Then loop through the sheets and mail them like this
>>
>> Change the sheet name here to your data sheet with all the data
>> If sh.Name <> "YourDataSheet" Then
>>
>>
>> Sub Mail_Every_Worksheet()
>> 'Working in 97-2007
>> Dim sh As Worksheet
>> Dim wb As Workbook
>> Dim FileExtStr As String
>> Dim FileFormatNum As Long
>> Dim TempFilePath As String
>> Dim TempFileName As String
>>
>> TempFilePath = Environ$("temp") & "\"
>>
>> If Val(Application.Version) < 12 Then
>> 'You use Excel 97-2003
>> FileExtStr = ".xls": FileFormatNum = -4143
>> Else
>> 'You use Excel 2007
>> FileExtStr = ".xlsm": FileFormatNum = 52
>> End If
>>
>> With Application
>> .ScreenUpdating = False
>> .EnableEvents = False
>> End With
>>
>> For Each sh In ThisWorkbook.Worksheets
>> If sh.Name <> "YourDataSheet" Then
>>
>> sh.Copy
>> Set wb = ActiveWorkbook
>>
>> TempFileName = "Sheet " & sh.Name & " of " _
>> & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
>>
>> With wb
>> .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
>> On Error Resume Next
>> .SendMail "", _
>> "This is the Subject line"
>> On Error GoTo 0
>> .Close SaveChanges:=False
>> End With
>>
>> Kill TempFilePath & TempFileName & FileExtStr
>>
>> End If
>> Next sh
>>
>> With Application
>> .ScreenUpdating = True
>> .EnableEvents = True
>> End With
>> End Sub
>>
>>
>>
>>
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Jordan" <(E-Mail Removed)> wrote in message news:6B028D2B-460E-48B8-9B1C-(E-Mail Removed)...
>> > No. I was hoping to have each email saved as a draft or left open so the
>> > address could be added. Thanks for your help.
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Hi Jordan
>> >>
>> >> Is the mail addresss of each carrier also in the table ?
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "Jordan" <(E-Mail Removed)> wrote in message news:CC36060B-6232-4BE2-BFC9-(E-Mail Removed)...
>> >> >I have an Excel file with 198 rows and several columns. One of the columns
>> >> > is a list of the different "carriers". Out of the 198 rows there is only
>> >> > 10-12 different carriers. The rest of the columns are misc data.
>> >> >
>> >> > I need to send this information out to each carrier but can only show each
>> >> > carrier their own information. In otherwords; I need to sort by the column
>> >> > listing the carrieres and then divide the information up by each unique value
>> >> > in that column.
>> >> >
>> >> > Is there anyway to automate this? Any help will be greatly apprciated.
>> >> >
>> >> > Cindy.
>> >> >
>> >> >
>> >>
>>

(1) When the multiple files are created some rows are red. I looked to see
if it had something to do with the filtering when the file was created. For
example; if the file was created from lines 2, 7, 8, 9, 12, & 14 then it
would cause 2 to be red, 7, 8, & 9 to be white and then 12 to be red again
and so forth. This does not seem to be the case and I dont see anything
other patterns.
(2) I will try this and see what happens. And I will look at the link JP
sent me to see if I can find how to add text to the body. All the
information you have posted there is very helpful.


"Ron de Bruin" wrote:

> >(1) Is there a way to get rid of the red cell highlighing in the created
> >files.
>
> Sorry I not understand what you mean with this
>
> 2: You can call the mail macro in the splt macro in the last line like
>
> Call Mail_Every_Worksheet
>
> If you use Outlook you can also use another macro that you can use to add text in the body
>
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Jordan" <(E-Mail Removed)> wrote in message news:90EA36D9-5348-4C45-B33F-(E-Mail Removed)...
> > JP - Thanks for the lead.
> >
> > Ron de Bruin. Thank you. Thank you. Thank you. I used the macro and it
> > does exactly what we need it to do. This is going to save many hours in a
> > day. I have a couple of questions, if you dont mind.
> > (1) Is there a way to get rid of the red cell highlighing in the created
> > files.
> > (2) I am really new to this. How do I add the second set of code to the
> > first.
> >
> > Again, this is very helpful so many thanks.
> >
> > "Ron de Bruin" wrote:
> >
> >> Use the code to split the data in different sheets
> >> See reply from JP
> >>
> >> Then loop through the sheets and mail them like this
> >>
> >> Change the sheet name here to your data sheet with all the data
> >> If sh.Name <> "YourDataSheet" Then
> >>
> >>
> >> Sub Mail_Every_Worksheet()
> >> 'Working in 97-2007
> >> Dim sh As Worksheet
> >> Dim wb As Workbook
> >> Dim FileExtStr As String
> >> Dim FileFormatNum As Long
> >> Dim TempFilePath As String
> >> Dim TempFileName As String
> >>
> >> TempFilePath = Environ$("temp") & "\"
> >>
> >> If Val(Application.Version) < 12 Then
> >> 'You use Excel 97-2003
> >> FileExtStr = ".xls": FileFormatNum = -4143
> >> Else
> >> 'You use Excel 2007
> >> FileExtStr = ".xlsm": FileFormatNum = 52
> >> End If
> >>
> >> With Application
> >> .ScreenUpdating = False
> >> .EnableEvents = False
> >> End With
> >>
> >> For Each sh In ThisWorkbook.Worksheets
> >> If sh.Name <> "YourDataSheet" Then
> >>
> >> sh.Copy
> >> Set wb = ActiveWorkbook
> >>
> >> TempFileName = "Sheet " & sh.Name & " of " _
> >> & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
> >>
> >> With wb
> >> .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
> >> On Error Resume Next
> >> .SendMail "", _
> >> "This is the Subject line"
> >> On Error GoTo 0
> >> .Close SaveChanges:=False
> >> End With
> >>
> >> Kill TempFilePath & TempFileName & FileExtStr
> >>
> >> End If
> >> Next sh
> >>
> >> With Application
> >> .ScreenUpdating = True
> >> .EnableEvents = True
> >> End With
> >> End Sub
> >>
> >>
> >>
> >>
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "Jordan" <(E-Mail Removed)> wrote in message news:6B028D2B-460E-48B8-9B1C-(E-Mail Removed)...
> >> > No. I was hoping to have each email saved as a draft or left open so the
> >> > address could be added. Thanks for your help.
> >> >
> >> > "Ron de Bruin" wrote:
> >> >
> >> >> Hi Jordan
> >> >>
> >> >> Is the mail addresss of each carrier also in the table ?
> >> >>
> >> >> --
> >> >>
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl/tips.htm
> >> >>
> >> >>
> >> >> "Jordan" <(E-Mail Removed)> wrote in message news:CC36060B-6232-4BE2-BFC9-(E-Mail Removed)...
> >> >> >I have an Excel file with 198 rows and several columns. One of the columns
> >> >> > is a list of the different "carriers". Out of the 198 rows there is only
> >> >> > 10-12 different carriers. The rest of the columns are misc data.
> >> >> >
> >> >> > I need to send this information out to each carrier but can only show each
> >> >> > carrier their own information. In otherwords; I need to sort by the column
> >> >> > listing the carrieres and then divide the information up by each unique value
> >> >> > in that column.
> >> >> >
> >> >> > Is there anyway to automate this? Any help will be greatly apprciated.
> >> >> >
> >> >> > Cindy.
> >> >> >
> >> >> >
> >> >>
> >>
>

> (1) When the multiple files are created some rows are red
No idea what is going on.

If you can't find it then send me the file private with the code you use and
I will look at it after work tomorrow

For text in the body you can adapt this macro
http://www.rondebruin.nl/mail/folder2/mail5.htm




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" <(E-Mail Removed)> wrote in message news:9B11E1E8-8ED3-4820-838B-(E-Mail Removed)...
> (1) When the multiple files are created some rows are red. I looked to see
> if it had something to do with the filtering when the file was created. For
> example; if the file was created from lines 2, 7, 8, 9, 12, & 14 then it
> would cause 2 to be red, 7, 8, & 9 to be white and then 12 to be red again
> and so forth. This does not seem to be the case and I dont see anything
> other patterns.
> (2) I will try this and see what happens. And I will look at the link JP
> sent me to see if I can find how to add text to the body. All the
> information you have posted there is very helpful.
>
>
> "Ron de Bruin" wrote:
>
>> >(1) Is there a way to get rid of the red cell highlighing in the created
>> >files.
>>
>> Sorry I not understand what you mean with this
>>
>> 2: You can call the mail macro in the splt macro in the last line like
>>
>> Call Mail_Every_Worksheet
>>
>> If you use Outlook you can also use another macro that you can use to add text in the body
>>
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Jordan" <(E-Mail Removed)> wrote in message news:90EA36D9-5348-4C45-B33F-(E-Mail Removed)...
>> > JP - Thanks for the lead.
>> >
>> > Ron de Bruin. Thank you. Thank you. Thank you. I used the macro and it
>> > does exactly what we need it to do. This is going to save many hours in a
>> > day. I have a couple of questions, if you dont mind.
>> > (1) Is there a way to get rid of the red cell highlighing in the created
>> > files.
>> > (2) I am really new to this. How do I add the second set of code to the
>> > first.
>> >
>> > Again, this is very helpful so many thanks.
>> >
>> > "Ron de Bruin" wrote:
>> >
>> >> Use the code to split the data in different sheets
>> >> See reply from JP
>> >>
>> >> Then loop through the sheets and mail them like this
>> >>
>> >> Change the sheet name here to your data sheet with all the data
>> >> If sh.Name <> "YourDataSheet" Then
>> >>
>> >>
>> >> Sub Mail_Every_Worksheet()
>> >> 'Working in 97-2007
>> >> Dim sh As Worksheet
>> >> Dim wb As Workbook
>> >> Dim FileExtStr As String
>> >> Dim FileFormatNum As Long
>> >> Dim TempFilePath As String
>> >> Dim TempFileName As String
>> >>
>> >> TempFilePath = Environ$("temp") & "\"
>> >>
>> >> If Val(Application.Version) < 12 Then
>> >> 'You use Excel 97-2003
>> >> FileExtStr = ".xls": FileFormatNum = -4143
>> >> Else
>> >> 'You use Excel 2007
>> >> FileExtStr = ".xlsm": FileFormatNum = 52
>> >> End If
>> >>
>> >> With Application
>> >> .ScreenUpdating = False
>> >> .EnableEvents = False
>> >> End With
>> >>
>> >> For Each sh In ThisWorkbook.Worksheets
>> >> If sh.Name <> "YourDataSheet" Then
>> >>
>> >> sh.Copy
>> >> Set wb = ActiveWorkbook
>> >>
>> >> TempFileName = "Sheet " & sh.Name & " of " _
>> >> & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
>> >>
>> >> With wb
>> >> .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
>> >> On Error Resume Next
>> >> .SendMail "", _
>> >> "This is the Subject line"
>> >> On Error GoTo 0
>> >> .Close SaveChanges:=False
>> >> End With
>> >>
>> >> Kill TempFilePath & TempFileName & FileExtStr
>> >>
>> >> End If
>> >> Next sh
>> >>
>> >> With Application
>> >> .ScreenUpdating = True
>> >> .EnableEvents = True
>> >> End With
>> >> End Sub
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "Jordan" <(E-Mail Removed)> wrote in message news:6B028D2B-460E-48B8-9B1C-(E-Mail Removed)...
>> >> > No. I was hoping to have each email saved as a draft or left open so the
>> >> > address could be added. Thanks for your help.
>> >> >
>> >> > "Ron de Bruin" wrote:
>> >> >
>> >> >> Hi Jordan
>> >> >>
>> >> >> Is the mail addresss of each carrier also in the table ?
>> >> >>
>> >> >> --
>> >> >>
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl/tips.htm
>> >> >>
>> >> >>
>> >> >> "Jordan" <(E-Mail Removed)> wrote in message news:CC36060B-6232-4BE2-BFC9-(E-Mail Removed)...
>> >> >> >I have an Excel file with 198 rows and several columns. One of the columns
>> >> >> > is a list of the different "carriers". Out of the 198 rows there is only
>> >> >> > 10-12 different carriers. The rest of the columns are misc data.
>> >> >> >
>> >> >> > I need to send this information out to each carrier but can only show each
>> >> >> > carrier their own information. In otherwords; I need to sort by the column
>> >> >> > listing the carrieres and then divide the information up by each unique value
>> >> >> > in that column.
>> >> >> >
>> >> >> > Is there anyway to automate this? Any help will be greatly apprciated.
>> >> >> >
>> >> >> > Cindy.
>> >> >> >
>> >> >> >
>> >> >>
>> >>
>>