I am working with a workbook in Excel 2003 that has over 40 tabs
(worksheets). Each worksheet is a department report and each department has 4
reports. The last report is the one I need to select for each department and
copy it into a new workbook. The name of the worksheets to be selected copied
and pasted is "xxxxx-RSS Upload" where x is the department number. I was
trying to find a property that could only select the "Upload" part of the
worksheet name as a common denominator to select multiple worksheets that end
with that text string. I started of like this:

Sub Macro3()
Dim ws As Worksheet
For Each ws In Worksheets
If UCase(Right(Trim(ws.Name), 6)) = "UPLOAD" Then
ws.Select
ws.Copy After:=Workbooks("newbook"). _
Worksheets(Workbooks("newbook").Worksheets.Count)
End If
Next ws

End Sub

The Macro stops and gets error '9': Subscript out of range

The debugger directs me to the following line:
ws.Copy After:=Workbooks("newbook"). _
Worksheets(Workbooks("newbook").Worksheets.Count)

If I remove this line the Macro works fine selecting only the tabs i want,
so I know I am halfway in the right direction. Can anybody come up with a
solution for this??? Thanks in advance for your help