pull data from another workbook based on first 3 characters of cell

[lpking2005]

hi, im in need of some big help!

Ive been trying this all day, but cant get my head around it. hope someone can help?

I currently have a workbook "403 InfoHub July.xls" which has some cells that need to be populated from another workbook.

What happens is when i input a job jumber, it gives me all the required info for an operator to do there daily job.
One of these things is a bulk code number "1ZH077A", next to this cell is a line of other blank cells that need to be populated from a bulk code criteria sheet in another workbook.

I need to use the first 3 characters of the bulk code eg:"1ZH" to open the correct workbook and then use the full bulk code "1ZH077A" to goto the correct sheet, each sheet is named after the full bulk code.

Then i need it to select a range and then populate the cells in "403 InfoHub July.xls"

Keep in mind that there is alot of different bulk codes eg: 1ZZ, 1ZA, 1HC....


I managed to get it to open the correct workbook and goto correct sheet but it will not get the info with simple select,copy & paste functions.

heres my current code:

Workbooks.Open "T:\Production\PROD MOULDING\Digital Line Manuals\IC LINE CRITERIA\Mouldings\" & Left(ThisWorkbook.Name, 3) _
            & "\" & Left(Range("I37"), 3) & " New Format Line Criteria.xls", 0, 0


    Sheets(ThisWorkbook.Sheets(1).Range("I37").Value).Select


    Range("G7:Q7").Select
    Selection.Copy
    Windows("403 InfoHub July.xls").Activate
    Range("K37:K38").Select
    ActiveSheet.Paste

I hope i havnt confused you all?!

Please any help would greatly be appreciated.!!!!

 

[AlphaFrog]

This is not tested, but try something like this...

Dim wsName As String

wsName = Sheets(1).Range("I37").Value

Workbooks.Open "T:\Production\PROD MOULDING\Digital Line Manuals\IC LINE CRITERIA\Mouldings\" & Left(ThisWorkbook.Name, 3) _
            & "\" & Left(wsName, 3) & " New Format Line Criteria.xls", 0, 0

ActiveWorkbook.Sheets(wsName).Range("G7:Q7").Copy _
    Destination:=Workbook("403 InfoHub July.xls").Sheets(1).Range("K37")

 

[lpking2005]

That didnt work either.

Its strange, because as soon as it opens the workbook to copy the range, excel doesnt even try to select that specified range.
It just stays on the same cell that you last saved on.

 

[velkugan]

have you tried the same using ADODB connection???

 

[AlphaFrog]

That didnt work either.

Its strange, because as soon as it opens the workbook to copy the range, excel doesnt even try to select that specified range.
It just stays on the same cell that you last saved on.

You don't have to Select cells to copy them. The code I provided doesn't select the cells on the opened workbook. It just tries to copy from it. I don't know why it's not working though.