Linking cells to several other workbooks and worksheets
 

Hi
I am trying to consolidate information from 12 workbooks, each with 12
worksheets (jan - dec) into one workbook (also split by month). I am
linking the cells but typing the following:
=[leisureclub1.xls]January!A1
=[leisureclub1.xls]January!A2
=[leisureclub1.xls]January!A3
=[leisureclub2.xls]January!A1
=[leisureclub2.xls]January!A2
and so on. This is very time consuming. Is it possible to have the
worksheet reference (January!) read from a cell in my consolidated
sheet to save me having to copy the link to the cells over and over i.e
= [liesureclub1.xls]"cell B7"!A1
= [liesureclub1.xls]"cell B7"!A2
= [liesureclub1.xls]"cell B7"!A3
I hope this makes sence. I think i have confused myself!
Webby

Linking cells to several other workbooks and worksheets
 

Sub writeformula()
for each cell in selection
cell.formula = "='[LeisureClub1.xls]" & range("B7").Value & _
"'!" & cell.Address(0,0)
Next
End Sub

would be a guess.

select the cells where you want the formula and run the macro.

--
Regards,
Tom Ogilvy

wrote in message
ups.com...
Hi
I am trying to consolidate information from 12 workbooks, each with 12
worksheets (jan - dec) into one workbook (also split by month). I am
linking the cells but typing the following:
=[leisureclub1.xls]January!A1
=[leisureclub1.xls]January!A2
=[leisureclub1.xls]January!A3
=[leisureclub2.xls]January!A1
=[leisureclub2.xls]January!A2
and so on. This is very time consuming. Is it possible to have the
worksheet reference (January!) read from a cell in my consolidated
sheet to save me having to copy the link to the cells over and over i.e
= [liesureclub1.xls]"cell B7"!A1
= [liesureclub1.xls]"cell B7"!A2
= [liesureclub1.xls]"cell B7"!A3
I hope this makes sence. I think i have confused myself!
Webby