auto-separating data for seperate reports

[kelbarben]

Hi. I have the following type columns that I need to create seperate reports for each service agent to see their own info. I've tried to write a macro but I can't get to work because I'm constantly adding more rows to it each day. I have over 50 service agents that need to see their own rows (i need them to see all of the info as it is) and cannot see the other service agents' info. They don't have Access so I have to do it in Excel. Do any of you have any ideas on how to pull into their own report so I can send it to each of them on their own without having to manually do it daily? Thanks!

kelbarben

<TABLE style="WIDTH: 675pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=898 border=0 x:str><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 239pt; mso-width-source: userset; mso-width-alt: 11629" width=318><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl26 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #c3cbcb 1pt solid; BORDER-LEFT: #c3cbcb 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.2pt; BACKGROUND-COLOR: #4b6b85" width=90 height=20>Account Code</TD><TD class=xl27 style="BORDER-RIGHT: #c3cbcb 1pt solid; BORDER-TOP: #c3cbcb 1pt solid; BORDER-LEFT: #c3cbcb 1pt solid; WIDTH: 105pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #4b6b85" width=140>Caller Name</TD><TD class=xl27 style="BORDER-RIGHT: #c3cbcb 1pt solid; BORDER-TOP: #c3cbcb 1pt solid; BORDER-LEFT: #c3cbcb; WIDTH: 44pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #4b6b85" width=58>Service Status</TD><TD class=xl27 style="BORDER-RIGHT: #c3cbcb 1pt solid; BORDER-TOP: #c3cbcb 1pt solid; BORDER-LEFT: #c3cbcb; WIDTH: 239pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #4b6b85" width=318>Service Agent</TD><TD class=xl28 style="BORDER-RIGHT: #c3cbcb 1pt solid; BORDER-TOP: #c3cbcb 1pt solid; BORDER-LEFT: #c3cbcb; WIDTH: 72pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #4b6b85" width=96>Activation Date</TD><TD class=xl28 style="BORDER-RIGHT: #c3cbcb 1pt solid; BORDER-TOP: #c3cbcb 1pt solid; BORDER-LEFT: #c3cbcb; WIDTH: 66pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #4b6b85" width=88>Deactivation Date</TD><TD class=xl27 style="BORDER-RIGHT: #c3cbcb 1pt solid; BORDER-TOP: #c3cbcb 1pt solid; BORDER-LEFT: #c3cbcb; WIDTH: 81pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: #4b6b85" width=108>Component Code</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" width=90 height=20 x:num>193626</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BROWN CONNIE</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1050 MALL - DAWN SANDS</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DATABIS</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" width=90 height=20 x:num>216567</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AGUIRRE-CORTES</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1070 S SIOUX CITY - NANCY GOMEZ</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">GPREPAY</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" width=90 height=20 x:num>216569</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ESQUIUEL RAMIRO</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1070 S SIOUX CITY - CAITLIN WOLF</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">GPREPAY</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" width=90 height=20 x:num>216570</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">MUNOZ MARIA</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1050 MALL - ALEJANDRO DEANDA</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">GPREPAY</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" width=90 height=20 x:num>216571</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1050 MALL - DAWN SANDS</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">F500V</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" align=right width=90 height=20 x:num>216571</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">MAGANA SANDRA</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1050 MALL - DAWN SANDS</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">F500 ADDLINE</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" align=right width=90 height=20 x:num>216572</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">PEREZ JESUS</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1070 S SIOUX CITY - NANCY GOMEZ</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">GPREPAY</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" height=20 x:num>216573</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">FAYS DANIEL</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2050 SIOUX FALLS 41ST - AMBER DWYER</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">I1000V</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" height=20 x:num>216574</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1050 MALL - CHRIS JACKSON</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">F500V</TD></TR><TR style="HEIGHT: 15.2pt; mso-height-source: userset" height=20><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.2pt; BACKGROUND-COLOR: transparent" width=90 height=20 x:num>216574</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">HEMMELRICK KIMB</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">A</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1050 MALL - CHRIS JACKSON</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="39873">3/1/2009</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">F500 ADDLINE</TD></TR></TBODY></TABLE>

 

[Trebor76]

Re: auto-seperating data for seperate reports

Hi kelbarben,

Not sure how many records you have, but let me know how the following goes - just change the 'strReportDirectory' variable from 'C:\' to whatever directory you want the individual files saved in:

Sub Macro1()

    Dim strSourceTab As String
    Dim strActiveCell As String
    Dim intSheetsInWkb As Integer
    Dim strReportDirectory As String
    Dim lngLastRow As Long
    Dim intFileCount As Integer
    Dim lngCopyStartRow As Long
    Dim lngCopyEndRow As Long
    Dim strReportName As String
    
    Application.ScreenUpdating = False
    
    strSourceTab = ActiveSheet.Name
    strActiveCell = ActiveCell.Address
    
    intSheetsInWkb = Application.SheetsInNewWorkbook
    
    'Change to whatever directory you want the individual reports _
    to be saved in.
    strReportDirectory = "C:\"
    
    'Make a copy of the active sheet and put it at the end _
    of the current workbook so the initial tab is not altered.
    ActiveSheet.Copy After:=Worksheets(Worksheets.Count)
    
    'Assumes the last row can be found from Column D - _
    change if required.
    lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    strWorkingTab = ActiveSheet.Name
    
    Range("A2:G" & lngLastRow).Sort _
        Key1:=Range("D2"), _
        Order1:=xlAscending, _
        Header:=xlNo
    
    'Data starting row
    lngCopyStartRow = 2
    
    'Set the number of sheets in each new book to one.
    Application.SheetsInNewWorkbook = 1
    
    For Each cell In Range("D2:D" & lngLastRow)
    
        If cell.Value <> cell.Offset(1, 0).Value Then
            
            lngCopyEndRow = cell.Row
            Range("A" & lngCopyStartRow & ":G" & lngCopyEndRow).Copy
            Workbooks.Add
            Range("A2").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            'Put headers in new created tab
            Range("A1").Value = "Account Code"
            Range("B1").Value = "Caller Name"
            Range("C1").Value = "Service Status"
            Range("D1").Value = "Service Agent"
            Range("E1").Value = "Activation Date"
            Range("F1").Value = "Deactivation Date"
            Range("G1").Value = "Component Code"
            'Make Columns A-G (inclusive) best-fit
            Columns("A:G").EntireColumn.AutoFit
            'Format the date Column (E)
            Columns("E:E").NumberFormat = "mm/dd/yyyy"
            'Active cell is A2
            Range("A2").Select
            'Create filename as service agent
            strReportName = Range("D2").Value & ".xls"
            'Turn off error displays (if the workbook already exists _
            it will be overwritten - no questions asked).
            Application.DisplayAlerts = False
            'Save the new workbook and close it
            With ActiveWorkbook
                .SaveAs strReportDirectory & strReportName
                .Close
            End With
            'Turn on error displays.
            Application.DisplayAlerts = True
            
            intFileCount = intFileCount + 1
            lngCopyStartRow = lngCopyEndRow + 1
            
        End If
    
    Next cell
    
    'Turn off error displays.
    Application.DisplayAlerts = False
    
    ActiveSheet.Delete
    
    'Turn on error displays.
    Application.DisplayAlerts = True
    
    'Set the 'SheetsInNewWorkbook" setting back to original state
    Application.SheetsInNewWorkbook = intSheetsInWkb
    
    Application.ScreenUpdating = True
    
    Sheets(strSourceTab).Select
    Range(strActiveCell).Select
    
    MsgBox "The " & intFileCount & " individual service agent files have" & _
    " now been saved in the " & strReportDirectory & " directory.", _
    vbInformation, "Service Agent File Creator Editor"
    
End Sub

HTH

Robert