Just a small change:

I'd really use:

RngToCopy.Copy _
Destination:=DestCell

Instead of:

RngToCopy.Copy _
DestCell

I think it's more self-documenting.

There's no difference in functionality, though.

Dave Peterson wrote:

I'd use something like:

Option Explicit
Sub Dates_Try()

Dim WS_Count As Long
Dim iCtr As Long
Dim LastRow As Long
Dim RngToCopy As Range
Dim DestCell As Range

WS_Count = ActiveWorkbook.Worksheets.Count

For iCtr = 1 To WS_Count
Select Case UCase(Worksheets(iCtr).Name)
'if you're comparing to ucase, make sure
'you enter the value in upper case
Case Is = "USAGE UPLOAD"
'do nothing
Case Else
With Worksheets(iCtr)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set RngToCopy = .Range("a7:B" & LastRow)
End With

With Worksheets("Usage Upload")
Set DestCell _
= .Cells(.Rows.Count, "E").End(xlUp).Offset(1, 0)
End With

RngToCopy.Copy _
DestCell
End Select
Next iCtr

End Sub

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

--

Dave Peterson

--

Dave Peterson