How do I copy range from one worksheet and paste into new workbook

[citizenzen]

Greetings.

I am creating a small application that will allow me to copy specific data from a range of cells within one column of one excel worksheet to a new worksheet in a new workbook. How can I copy that specific data to the new worksheet? I am getting errors on the copy and paste portion. I am getting a Run-time error of 9, Subscript out of range. Also, is there a way to obtain the specific error?

Public Function findSystem()

Dim SysCell As Range
Dim System As String

'Excel Properties
Dim rCells As Range
Dim rRange As Range

Excel.Application.DisplayAlerts = False
Application.ScreenUpdating = False

For Each rCells In Worksheets(1).Range("A13:A49")
   
    If InStr(rCells, "SYSTEM NAME:") Then
    System = Mid(rCells, 23, 255)
       
    'worksheets to be copied    
    Workbooks("olddocument.xls").Activate
    Workbooks("olddocument.xls").Worksheets("20MAR08 Complete").Activate
    
    'Copy worksheet to workbook    
'*****ERROR BEGINS HERE*************
    Worksheets("20MAR08 Complete").Copy Before:=Workbooks("selectedData.xls").Sheets(1)

   'I want to copy the data from the 'System' variable
    
'this is wrong, so I commented it out
   ' Range(System).Copy("selectedData.xls").Sheets (1)
       
    Workbooks("selectedData.xls").Sheets(1).Activate
    Workbooks("selectedData.xls").Sheets(1).Activate.Range("A2:A10").Activate
    
    Exit For
    
    End If
    
   Next
    
On Error Resume Next
    
End Function

 

[SkipVought]

Please post VBA questions in Forum707.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[AnotherHiggins]

Please repost this VBA-specific question in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.