Sorting Data in Excel

[Luke.mcgrath]

Good Morning Guys,
i am in need of some help if it is at all possible...

i have an excel spread sheet that looks like this...
<TABLE style="WIDTH: 502pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=670 x:str><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" span=4 width=107><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8 0.5pt; BACKGROUND-COLOR: #ffcc00; WIDTH: 49pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl29 height=21 width=65><?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t201 path="m,l,21600r21600,l21600,xe" o:spt="201" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><vath o:connecttype="rect" fillok="f" strokeok="f" o:extrusionok="f" shadowok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock shapetype="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="Z-INDEX: 4; POSITION: absolute; MARGIN-TOP: 0px; WIDTH: 48.75pt; HEIGHT: 15.75pt; VISIBILITY: hidden; MARGIN-LEFT: 0px" id=_x0000_s1029 o:insetmode="auto" stroked="f" type="#_x0000_t201"><o:lock v:ext="edit" text="t" rotation="t"></o:lock></v:shape>Date</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffcc00; WIDTH: 49pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl27 width=65>Team</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffcc00; WIDTH: 84pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl25 width=112>Agent Name</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #99cc00; WIDTH: 80pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl26 width=107>Introduction</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #99cc00; WIDTH: 80pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl26 width=107>Rapport</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #99cc00; WIDTH: 80pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl26 width=107>Control</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #99cc00; WIDTH: 80pt; BORDER-TOP: #ece9d8 0.5pt; BORDER-RIGHT: #ece9d8 0.5pt" class=xl26 width=107>Listening</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl30 height=17 x:num="40673">10-May</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl28>RCC2</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24>xxxxxxxx</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24 align=right x:num>3</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24 align=right x:num>2</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24 align=right x:num>2</TD></TR></TBODY></TABLE>


now with this i need to sort all of the data that is created in may to be Copied into another worksheet called "may" is this at all possible????


thanks MASSIVLY for your future help
let me know if you need further information
Cheers
Luke McGrath

 

[saltkev]

Hi

Give this a try. it can be run as a spread sheet event or under a command button. You will have to change some of the ranges to suite your data, check where I have made comments. Cells "B1" & "C1" contain code information for the prog to use. "B1" month number in '05 format for May. "C1" is the name of the sheet where you want the data to finish up in this case may. These locations can be changed just tell the code where to look.

Dim month, mcopy
Application.ScreenUpdating = False
mcopy = Sheets("sheet1").Range("c1").Value ' location of sheet name to copy

' *** Sort Data ***
Sheets("sheet1").Range("A5:G22").Select  'Range of Data to sort including titles
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A6:A17") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A5:G17")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

' *** Find & Copy/Paste Data ***
For Each month In Sheets("sheet1").Range("a6:A17") 'Where to look

        ' *** Search Criteria. Note Range "B1" Where Month Number is Stored (number 5 for May) ***
        If month Like "???" & Sheets("Sheet1").Range("b1").Value & "*" Then ' search criteria
        month.EntireRow.Copy
         
        Sheets(mcopy).Activate ' location of sheet name to copy
        
        ActiveSheet.Range("a6").Select
        
                          
               
              If Sheets(mcopy).Range("a6").Value = "" Then ' Paste 1st line
               Sheets(mcopy).Range("a6").Select
               ActiveSheet.Paste
               GoTo ender
               
              End If
        
        
        Sheets(mcopy).Activate
          
            If Sheets(mcopy).Range("a6").Value > "" And Sheets(mcopy).Range("a7").Value = "" Then ' Paste 2nd line
               Sheets(mcopy).Range("a7").Select
               ActiveSheet.Paste
               GoTo ender
               
            End If
        
        Sheets(mcopy).Activate
          
            If Sheets(mcopy).Range("a6").Value > "" And Sheets(mcopy).Range("a7").Value > "" Then 'Paste Subsequent Lines to first avail line
               Sheets(mcopy).Range("a6").Select
               Selection.End(xlDown).Select
               Selection.Offset(1, 0).Select
               ActiveSheet.Paste
               GoTo ender
               
            End If
    
ender:
              Sheets("sheet1").Activate
        
        
        End If
      
       
Next month

regards

Saltkev