good day,

I have workbook A and workbook B;

in workbook A cell C9 has a value (31); in workbook B, there is a worksheet
named 31; regardless of the number in the cell, there will always be a
worksheet in WorkbookB with the same value (if C9 was 55, there will be a
sheet named 55 in WorkbookB, etc.)

i want to retrieve a specific piece of data from Workbook B, Work Sheet
named 31, cell B5 back into WorkbookA; i have to do this for about 150 cells
so i want to just find the formula and copy it down.

i know i'm supposed to reference the value in cell C9, but i'm stuck.

any help to get me going?

jat

Try this( UN tested)

sub makeformulas()
dim shtnum as long
dim i as long

shtnum=range("c9").value
for i = 1 to 150
cells(i,1).formula="=[workbookb.xls]sheet" & shtnum " &!b5"
shtnum=shtnum+1
next i
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jat" wrote in message
...
good day,

I have workbook A and workbook B;

in workbook A cell C9 has a value (31); in workbook B, there is a
worksheet
named 31; regardless of the number in the cell, there will always be a
worksheet in WorkbookB with the same value (if C9 was 55, there will be a
sheet named 55 in WorkbookB, etc.)

i want to retrieve a specific piece of data from Workbook B, Work Sheet
named 31, cell B5 back into WorkbookA; i have to do this for about 150
cells
so i want to just find the formula and copy it down.

i know i'm supposed to reference the value in cell C9, but i'm stuck.

any help to get me going?

jat