retrieve data from different workbook

[jat]

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

 

[Don Guillett]

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