Hi

I am new to VBA. I am trying to write code to go through each worksheet to sort data. However, when I execute it is only sorting the current sheet and not looping though all of the worksheets.

Below is my code. Can someone help!!

Thanks.

Dim xlSheet As Excel.Worksheet
Set xlSheet = ActiveSheet
Dim Rng As Range
Set Rng = Range(Range("A2").End(xlToRight), Range("A2").End(xlDown))

    For Each xlSheet In ActiveWorkbook.Worksheets
        Rng.Sort Key1:=Range(Range("A2").End(xlToRight), Range("A2").End(xlDown)), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Next
End Sub

2 things to try:

1 Make the first line in the For Each loop
       xlSheet.Activate

2 Put the Set rng line in the loop

A quick experiment suggests this will fix it.

Selby

This is what I did .

On the VB fourm they pick what column to sort on by a combo box

and the code to do the sort in excel is:

lnRow = last row in the spread sheet

lcStartRow = the row the reports data start on since I have a header on the spread sheet





   Select Case Me.cmbReportType.ListIndex
        Case Is = 0
            Select Case Me.cmbSortOn.ListIndex
                Case Is = 0
                    lcCellToSortOn = "A"
                Case Is = 1
                    lcCellToSortOn = "B"
                Case Is = 2
                    lcCellToSortOn = "C"
                Case Is = 3
                    lcCellToSortOn = "D"
                Case Is = 4
                    lcCellToSortOn = "E"
                Case Is = 5
                    lcCellToSortOn = "F"
            End Select

            lcStartRow = CStr(lnStartRow + 1)
            lcCellToSortOn = lcCellToSortOn & lcStartRow
            lcCellSelect = "A" & lcStartRow & ":F" & CStr(lnRow)

            With goExcel
                .Range(lcCellSelect).Sort .Range(lcCellToSortOn)
            End With
    End Select