You need your loop to do the checking, and exclusion, not a Case statement
containing the loop. Assuming your code does what you want, try structuring
it this way and see if it works for you...

Sub Dates_Try()
Dim WS_Count As Integer
Dim I As Integer
Application.ScreenUpdating = False
For I = 1 To WS_Count
If Worksheets(I).Name < "Usage Upload" Then
Worksheets(I).Activate
'Range("A7").End(xlDown).End(xlRight).Select
Range("A7:B7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Usage Upload").Select
Range("E65536").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
I = 1 + 1
End If
Next I
End Sub

--
Rick (MVP - Excel)



"Aaron Bartee" <Aaron wrote in message
...
I am trying to loop through every worksheet and select and copy a range of
dates (varrying lenghts) and paste in the first worksheet ('Usage
Upload').
this is the code I have now but i cannot get the Select Case expression to
work.

Please help!


Sub Dates_Try()

Application.ScreenUpdating = False


Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

Select Case UCase(Worksheet.Name)

Case "Usage Upload"

Case Else


For I = 1 To WS_Count


Worksheets(I).Activate

'Range("A7").End(xlDown).End(xlRight).Select
Range("A7:B7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Usage Upload").Select
Range("E65536").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste

I = 1 + 1


Next I

End Select


End Sub