Getting cell number using cell value in the file

[Javed07]

I have multiple excel file which has product information , i need to copy paste this data to new workbook

i can copy all the values in sample till product (cell A10)

but the cell product visibility position in sample is A16, this may not be same for all files. it could be A20 for one file

i need to get cell value for this 'Product visiblity' like for the sample i need A16 , based on this i would copy B16 value in other words i need 16 as return value for this sample .

how can i do this in vba, it could change from file to file i cannot hardcode this

please help with this code


thanks

[haripopuri]

Will you be looking for "Product Visibility" all the time? Did you consider using "Find" for this word and retrieving value of a cell which is one cell right to where you find this word?