Copy Cell in each Worksheet in Workbook and Paste Values into new Worksheet

[esquiresurf]

Hi



I would like my macro to do the following:

2. Go through each worksheet in a workbook
3. Find a cell value that is in all worksheets
4. Copy the cell that is two cells to the right
5. Paste values into a Worksheet called “Daily Net Movement”

However the error I receive is: “Object Variable or Withblock variable not set”






Here is the code:

Sub Collect_Net_Movement()



Application.ScreenUpdating = False




Dim ws As Worksheet



Dim ws1 As Worksheet



    Set ws1 =Worksheets("Daily Net Movement")


    ws1.Select


 i = 14



For Each ws In ThisWorkbook.Worksheets


 'find cell with text "Agreed Collateral Move" andcopy cell 2 cells to the right
Cells.Find("Agreed Collateral Move").Select 'Errorreceived on this line



ActiveCell.Offset(0, 2).Copy





'paste values in worksheet("Daily Net Movement")starting from Cell E14



With ws1.Range("E" & i)


    .PasteSpecialxlPasteValues
  



End With


i = i + 1



Next ws


Application.ScreenUpdating = True


End Sub

All worksheet names are different.




Any assistance in correcting this code would be greatlyappreciated.

Thank you

 

[steve the fish]

See if this works:

Set ws1 = Worksheets("Daily Net Movement")
i = 14

For Each ws In ThisWorkbook.Worksheets
    Set c = ws.Cells.Find("Agreed Collateral Move", LookIn:=xlValues, LookAt:=xlWhole)
    If Not c Is Nothing Then
        ws1.Range("E" & i).Value = c.Offset(0, 2).Value
        i = i + 1
    End If
Next ws

 

[esquiresurf]

Thank you. That worked very well.