Hi,

Based on the codes below, my intended result is to loop through under
the same workbook, within the worksheet array ( ie from visible sheetX
till end ) to copy range based on text string search( "*Total*" ) in
column A of visible sheets("X"), say text string found in A3 then will
copy range ("C3:H3") from sheets("X") and paste it to the last row
column B of visible sheets("X (2)") and so on until the last visible
sheet.
Only visible sheets within worksheet array will have duplicate sheets
denoted as "(2)" at the end of sheet name and total visible sheets at
least 2 and may be more which depends on the workbook available from
other source

After running the codes, the results fails to achieve the above
objective because the same worksheet copy back to the same worksheet
again

Set WB = Workbooks("ABC.xls")
With WB
NumShts = .Worksheets.Count
ReDim myArr(1 To NumShts)
For myCount = 1 To NumShts
ShtName = .Sheets(myCount).Name
If Worksheets(ShtName).Visible = True Then
If ShtName = ShtName1 Then
With Worksheets(ShtName)
iLoop = WorksheetFunction.CountIf(.Columns(1), "*Total*")
Set rNA = .Range("A1")
For u = 1 To iLoop
Set rNA = .Columns(1).Find(What:="*Total Operating
Expenses*", After:=rNA, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=True)
Worksheets(ShtName1).Select
Dim Ltrow As Long
Ltrow = Range("B" & Rows.Count).End(xlUp).Row + 1
rNA.Offset(0, 1).Resize(1, 5).Copy
Worksheets(ShtName1).Range("B" & Ltrow)
Next u
End With
End If
End If
Next myCount
End With

Any help will be much appreciated and thanks in advance


Regards
Len