Pull data from external workbook to active workbook based on a specific cell value

[charchar001]

Hey guys,

I'm in somewhat of a pickle. I have two workbooks, the first lets call "tool.xlsm" and the other "hours.xlsx", what i am trying to do is within "tool" the user will input a value in cell Q9 and from there I need to run a macro to take that value and search that value in "hours" by either using the sheet name which is laid out as "shortname (user value)" or within the sheet itself which would be in the format of "2-user value". This workbook will always be adding a new sheet, so the code has to cycle threw all of them. Once that sheet has been located it needs to run a VLOOKUP to match available options from the Hours worksheet to the tools worksheet.

So here is how sheets are titled in Hours:

Data within sheet:

Man power	411
Time	1000
Part	1555
Alarm	111
Package	526
test	45

Not every sheet will have every option hence the Vlookup to place the data in the correct location.

The code i have is somewhat a hodge podge of multiple attempts:
Tools WB is the only one open but my code opens hours to attempt to use the user input to correct it.
My issue is that i can't get the sheet to change or search for the one i need.
As far as vlookup goes I won't know the sheet name so I can't create a link to it and hard code it which I know how to do.
I am aware the Collect data sub is not encompassing of all the subs only because i was trying to troubleshoot.
I am trying data query but not that great with it. If you think that is a better option I am all for it.

Sub Collect_Data()
Dim PID As String
Dim shrtnme As String
Dim shtnme As String
PID = Range("Q9").Value
shrtnme = Range("Q8").Value
'Open workbook
open_hours
End Sub

Sub open_hours()

Workbooks.Open _
    Filename:="C:\Users\chaudri\Documents\tool test files\Estimated Package Hours.xlsx"
    ReadOnly = True
On Error Resume Next
    ThisWorkbook.Sheets(Worksheets("Instruction").Range("n18").Value).Activate
End Sub
Sub Find_Hours()
    Dim strWSName As String
    
    strWSName = InputBox("Enter the PID to serach for")
    If strWSName = vbNullString Then
        MsgBox "can't find"
        Exit Sub
    End If
    
If SheetExists(strWSName) Then
        Worksheets(strWSName).Activate
    Else
        'look if it at least contains part of the name
        Dim s As Worksheet
        For Each s In ThisWorkbook.Sheets
            If InStr(s.Name, strWSName) > 0 Then
                s.Activate
                Exit Sub
            End If
        Next s
        MsgBox "That sheet name does not exist!"
    End If
End Sub
Function SheetExists(strWSName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(strWSName)
    If Not ws Is Nothing Then SheetExists = True
End Function

 

[dmt32]

Thank you Dave! It does seem to work.

I was unaware of many of these functions so thanks for the things i can read up on!

Just to learn for future VBA endeavors, so

an array in this case using "arr(i, 1) = Trim(cell.Value)" acts like a vlookup function correct?

An array is variable that has elements that are used to store data of specified data type.
I used an array to collect data from the open workbook & then pass it back to calling procedure to populate your table

You mentioned Merged cells being an issue, can it be done in the future to create a sub to unmerge cells then remerge them? Or that even a feasible option as data sets may change?

you could do what you suggest but personally, I would avoid it.

As far as the worksheet change code. What is the purpose of "(Me)" in this "Call Collect_Data(Me)"? Is there a benefit to putting something in there? Or is it just calling a local sub?
I usually leave the parenthesis field empty, hence the question

Once again thank you!!
?

Me keyword is in this case, is the Worksheet Object which I use to pass to Collect_Data code.
Do not remove it.


Glad solution worked ok for you

Dave