need help with excell sheet to sheet macro or equivalent

[ubnjacked]

so i'm kinda new to excel and am having a bit of trouble with getting a row from "sheet 1", "sheet 2", "sheet 3" to ""sheet 5". the first part of this is locating text from column "U" then copying the entire row to "sheet 5".

i have been this macro out but i can only seem to get it to work on "sheet 1". when i try putting it on "sheet 2" or "sheet 3" it doesn't react the same and wont go through the same search process.

----------------------------------------------------------------------------------------

Sub SearchForString()


Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 4
LSearchRow = 4

'Start copying data to row 2 in sheet 5 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column U = "YES", copy entire row to T-shirt Tracking
If Range("U" & CStr(LSearchRow)).Value = "YES" Then

'Select row in sheet 1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into sheet 5 in next row
Sheets("sheet 5").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to sheet 1 to continue searching
Sheets("sheet 1").Select



End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub
---------------------------------------------------------------------------------------------------------

sorry if my formatting is off as well.

i need to use the same macro or something equivalent to get the information from "sheet 1,2,3,4" to sheet 5 at different times from different sheets during the month.

 

[rs2k]

Have a look at each worksheet loop

Something along the lines of;

Sub EachWS()


 Dim WS As Worksheet


 For Each WS In ActiveWorkbook.Worksheets

 If WS.Name <> "Sheet5" Then

 'your code here
 
 End If

 Next WS


 End Sub

 

[ubnjacked]

Have a look at each worksheet loop

Something along the lines of;

Sub EachWS()


 Dim WS As Worksheet


 For Each WS In ActiveWorkbook.Worksheets

 If WS.Name <> "Sheet5" Then

 'your code here
 
 End If

 Next WS


 End Sub

tried it and same results thanks for your help though

 

[rs2k]

See if this works for you;

Sub FindYes()
Dim DestSheet As Worksheet
Dim WS As Worksheet
Set DestSheet = Worksheets("Sheet5")
  
  Dim sRow       As Long     'row index on source worksheet
  Dim dRow       As Long     'row index on destination worksheet
 
dRow = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row

For Each WS In ActiveWorkbook.Worksheets

'Stop screen flickering
Application.ScreenUpdating = False

If WS.Name <> "Sheet5" Then
 WS.Select
  For sRow = 2 To Range("U65536").End(xlUp).Row
     'use pattern matching to find client anywhere in cell
     If Cells(sRow, "U") = "Yes" Then
        dRow = dRow + 1
        'Copy the entire row. Change copy to cut if you want to remove it
        Cells(sRow, "A").EntireRow.Copy Destination:=DestSheet.Cells(dRow, 1)

     End If
  Next sRow
End If

    'Delete empty rows in active sheet if using the cut command
 '   Columns("A:A").Select
 '   Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
 Next WS

Application.ScreenUpdating = True

End Sub

HTH
Colin