PDA

View Full Version : [SOLVED] Multisheet Search and Paste



shrek
01-09-2005, 08:52 AM
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
01-09-2005, 09:16 AM
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
01-09-2005, 09:32 AM
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
01-09-2005, 09:43 AM
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
01-09-2005, 09:56 AM
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://support.microsoft.com/default.aspx?scid=kb;en-us;124105)

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

:help
Hopefully this will make sense.

Shrek

gibbo1715
01-09-2005, 10:35 AM
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
01-09-2005, 06:35 PM
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
01-10-2005, 04:22 AM
Fantastic, just the job. thanks very much for everybodys help. You dont realise how appreciated it is.

S

shrek
01-10-2005, 07:09 AM
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
01-10-2005, 03:57 PM
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
01-12-2005, 02:47 PM
Many thanks mdmackillop, your help has made a huge difference to my rather steep learning curve.

Thanks again
S