Prev: Is there a formlula to replace cell shade color with another color
Next: Getting a formula to do the following:
From: jat on 8 Feb 2010 19:28
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

From: Don Guillett on 8 Feb 2010 19:54
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
dguillett(a)gmail.com
"jat" <jat(a)discussions.microsoft.com> wrote in message
news:97B7E48B-5C87-4EBD-B3BE-F7F0A2F053FC(a)microsoft.com...
> 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
>

 | 
Pages: 1
Prev: Is there a formlula to replace cell shade color with another color
Next: Getting a formula to do the following: