I am currently using office xp 2000 and have created code to search all sheets in workbook for a specified value and then copy the rows to a sheet called report, the problem is that the find function only picks up values from sheet1, however pastes the data the correct number of times, i.e. the number of times it found the value. I also need to turn the copy function to a paste special for just the data, not formulas. If anyone has any ideas i would really appreciate it as this is doing my head in. People have mentioned using Macro's to do this however I am not sure how i would go about this operation as i have only ever used VBA to generate soloutions.
Private Sub Find_POD_Button_Click()
Dim strFindPOD As String
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Worksheets
If WS.Name = "Report" Then GoTo doNext
strFindPOD = TextBox1.Text
On Error GoTo ErrorMessage
WS.Activate
Cells.Find(What:=strFindPOD, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).EntireRow.Copy Destination:=Worksheets("Report").Range("A65536").End(xlUp).Offset(1, 0)
doNext:
Next WS
Exit Sub
ErrorMessage:
MsgBox ("Please Re-enter POD Number as data entered does not exist")
End Sub
I have attached a sample workbook to demonstrate the problem.
I have to thank other forum members for thier input so far.
Thank you.