Multisheet Search and Paste

[shrek]

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.

[mdmackillop]

Try the following

Private Sub Find_POD_Button_Click()
Dim strFindPOD As String
Dim WS As Worksheet
strFindPOD = POD_Number_Input.Text
For Each WS In ActiveWorkbook.Worksheets
    If WS.Name = "Report" Then GoTo doNext
    On Error GoTo ErrorMessage
    WS.Select
ActiveSheet.Range("A:A").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

[shrek]

Brilliant exactly what i needed, thank you, however for some reason this only works on XP, the computers i am going to be using this sheet on are all using excel 2000, I had heard that excel 2000 had some difficulties with this function.

Have you any ideas?

Thanks
Shrek

[mdmackillop]

I don't see why there should be a problem, but let me know if there is. Your setup could be simplified slightly, as I've amended your code to search only Column A, your search string could be in any cell not in Column A, or entered by an inputbox, and your routine contained in a standard module, rather than a worksheet module. I see no reason why you cannot run your code from the Report page, rather than the Jan page, which may not always be the most appropriate.
MD

[shrek]

I quite aggree, it's only a simplified version of a 4mb sheet that is used to record working hours for staff. I really wanted to get a rough working version that i could learn from, then implement properly when back in the office.
However the problem with excel 2000 seems to stump me, i was sent the below links, but I'm not entirely sure what that means for my code.

http://support.microsoft.com/default.aspx?scid=kb;en-us;124105

http://www.mrexcel.com/board2/viewtopic.php?t=122918&highlight=


Hopefully this will make sense.

Shrek

[gibbo1715]

Have a look at the attachment, its a bit messy but does work in excel 2000, hopefully someone will have time to tidy the code up a bit


This is my first post here so hi to you all and hope this is useful

[mdmackillop]

Here's an alternative approach. You also need to change your seach to xlWhole. otherwise your search will find 59 in 259 for example.
MD

[shrek]

Fantastic, just the job. thanks very much for everybodys help. You dont realise how appreciated it is.

S

[shrek]

i say it's solved but one last problem.

Does anyone nkow how to make the copy. function values only. as I essentially want to use a paste special function in the code so that values only are copied over.

S

[mdmackillop]

Try changing your Find and Paste routine to

ActiveSheet.Range("A:A").Find(What:=strFindPOD, After:=Range("A1"), LookIn:=xlFormulas, LookAt _
            :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False).EntireRow.Copy
Worksheets("Report").Select
            Range("A65536").End(xlUp).Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteValues

[shrek]

Many thanks mdmackillop, your help has made a huge difference to my rather steep learning curve.

Thanks again
S