PDA

View Full Version : Seach Worksheet Names and Return Data



Peta
02-09-2008, 06:22 AM
Have been searching the forum and found some very useful codes for the project I am working on. Could really do with a few ideas on this one.

I have a separate worksheet for each customer with sales data from each transaction. The worksheets are named after each customer. I am trying to search the worksheet names for the customer and then seach within that specifc worksheet by job description etc. and return the information to a userform.
I have no idea how to do this and is driving me slightly crazy :offwall:

I would really appreciate any help on how to go about this.

tmptplayer
02-09-2008, 09:31 AM
Here is some code that will search for a worksheet with a specific name, and then search down a column for a certain piece of data. I put this in a command button on Sheet1, and in Sheet2 I have 'a' 'b' 'c' 'd' 'e' 'f' in each cell from C1 to C7. Does this help?

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim wb As Workbook
Dim searchSheetName As String
Dim searchCellVal As String
Dim x As Integer
Dim y As Integer

x = 3

Set wb = ActiveWorkbook()
searchSheetName = "Sheet2"
searchCellVal = "d"

For Each ws In wb.Worksheets
If ws.Name = searchSheetName Then
With ws
.Activate
For y = 1 To 5
If .Cells(y, x).Value = searchCellVal Then
.Cells(y, x + 1).Value = "Found Match!"
End If
Next
End With

End If
Next
End Sub

mikerickson
02-09-2008, 10:25 AM
This assumes that the job descriptions are in column C. Two lines need to be adjusted to meet your situation.

Dim customerName As String
Dim customerSheet As Worksheet
Dim descriptionString As String

On Error Resume Next
Set customerSheet = ThisWorkbook.Sheets(customerName)
On Error Goto 0

If customerSheet Is Nothing Then
MsgBox "No such customer"
Else
With customerSheet
If IsError(Match(descriptionString, .Range("c:c"), 0)) Then
MsgBox "Customer " & customerName & " does not have " & descriptionString
Else
MsgBox customerName & "'s " & descriptionString & " is in row " & Match(descriptionString, .Range("c:c"), 0)
End If
End With
End If

Peta
02-09-2008, 07:20 PM
Thanks for the input.
My data in worksheet "Main" comes from another workbook called "Job Instruction" from "Main" it is sorted into separate worksheets for each customer. I want to be able to set something up so that I can search the customers and then list job descriptions, products and pricing and return the selected job to sheet1.

I am also trying to run the macro "FilterCustomer" in this workbook from another workbook.

I have attached the file so you can see what i am trying to explain.

Thanks again!

Peta
02-12-2008, 04:42 AM
Still trying to work this out, I think this is way over my head!

Can any one help?