It looks like your loop is in the wrong place to start with. You are
resetting your I value during the loop, which can cause issues. You also do
not need the UCase statement.

It looks like you are trying to copy data from every sheet into the Usage
Upload Sheet.

if so then I think it should look like this

Sub Dates_Try()

Application.ScreenUpdating = False

Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count

Select Case Sheets(I).Name

Case "Usage Upload"

Case Else

Worksheets(I).Activate
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
End Select

Next I


End Sub
--
If this helps, please remember to click yes.


"Aaron Bartee" wrote:

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