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

[citizenzen]

Hello.

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]

The ROW for this statement never changes...

ws_dest.Cells([red][b]2[/b][/red], 1 + i) = Mid(rCells.Value, sVal(1, i))

You need to do something like this...

[b]
dim lRow as long
lRow=2[/b]
    For i = 0 To UBound(sVal, 2)        
        For Each rCells In ws_source.Range("A16: A49")
        
        If InStr(rCells, sVal(0, i)) Then        
            ws_dest.Cells([b]lRow[/b], 1 + i) = Mid(rCells.Value, sVal(1, i))
           Set rCells = ws_source.Cells.Find(sVal(0, i))[b]
           lRow = lRow + 1[/b]
         End If
        Next
    Next

Skip,

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

 

[citizenzen]

wow! which of the numbers can be changed? for example:

The first system found:

System: Hybrid Toyota
Service: 1997
Unit Address: 110 Main
State: CA

System: Corolla
Service: 2006
Unit Address: 1810 Crescent
State: MD

Each should be on one row, but in respective columns for the categories. Now, they're in the worksheet, but skips cells:

System Unit Address State

Hybrid Toyota
Corolla


110 Main
1810 Crescent

CA
MD

 

[citizenzen]

I got it!!!


Thank you SkipVought for your patience!!!! Thank you everyone

 

[SkipVought]

Think about your looping process.

FIRST you search for array element 1 ("SYSTEM NAME:") for every cell in ("A16: A49")

THEN you search for then NEXT array element, ("SERVICE:") for every cell in ("A16: A49")

Is that what you intended? I think not, but you have to arrange the order of things AND when lRow gets indexed.



Skip,

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