Ihave 4 sheet with similar kind of structure; leftmost column has the
product code and other columns have details (numbers) about those
products.

ProdID Qty
1/art-05-d 200
1/art-05-q 204
1/art-05-s 420

I need to get the qty value and pass it to another sheet to same row
with corresponding ProdID. I cannot paste the whole columns, since not
all of the sheets have same amount of products. So the question is how
can i use the prodID as a "primary key"

Help is greatly appreciated.

I answered a similar question earlier - perhaps you
missed it...

To FETCH existing data run the following code



Sub FetchAllFiles()
'will open all files in a directory
Dim myPath As String, MyFile As String
Dim WB As Workbook, sheetname As String
Dim rw As Long

myPath = "H:\Excel_Demos\"

MyFile = Dir(myPath & "*.xls")

Do While MyFile < ""

rw = rw + 1

Set WB = Workbooks.Open(myPath & MyFile, False, True)
sheetname = WB.ActiveSheet.Name
WB.Close False

Cells(rw, 1) = MyFile
Cells(rw, 2) = sheetname

MyFile = Dir()
Loop


End Sub

The code opens each file it finds, gets the active
sheet's name, closes th efile without saving it, then
pushes the filename & sheet name to your active sheet.

update columns 3 & 4 with the new file name in 3 ("C")
and the new sheet name in 4 ("D") then run the following
procedu

Sub UpdateAllFiles()
'will open all files in a directory
Dim myPath As String, MyFile As String
Dim WB As Workbook, sheetname As String, newFilename As
String
Dim rw As Long

myPath = "H:\Excel_Demos\"

rw = 1

Do While Cells(rw, 1) < ""

MyFile = Cells(rw, 1).Value
newFilename = Cells(rw, 3).Value
sheetname = Cells(rw, 4).Value
Set WB = Workbooks.Open(myPath & MyFile, False, True)
WB.ActiveSheet.Name = sheetname
WB.Close True

Name myPath & MyFile As myPath & newFilename

rw = rw + 1
Loop

End Sub

this reads down column 1, fetches the file, renames the
sheet, cloases & saves the file, then renames the file.

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Ihave 4 sheet with similar kind of structure; leftmost
column has the
product code and other columns have details (numbers)
about those
products.

ProdID Qty
1/art-05-d 200
1/art-05-q 204
1/art-05-s 420

I need to get the qty value and pass it to another sheet
to same row
with corresponding ProdID. I cannot paste the whole
columns, since not
all of the sheets have same amount of products. So the
question is how
can i use the prodID as a "primary key"

Help is greatly appreciated.
.