Linking a several workbooks and worksheets

[Justin]

Hi
I am trying to consolidate information from 12 different workbooks
each with 12 worksheets (Jan-Dec) into one 'Group' Workbook (also
split by month). At present I am linking the cells as follow:
=[leisureclub1.xls]January!$F$16
=[leisureclub1.xls]January!$F$17
=[leisureclub1.xls]January!$F$18
=[leisureclub2.xls]January!$F$16
=[leisureclub2.xls]January!$F$17
and so on. this is very time consuming. Is there any way i can specify
the worksheet refernce from a cell in my consolidated spreadsheet i.e
=[leisureclub1.xls]"cell A1"!$F$16
=[leisureclub1.xls]"cell A1"!$F$17
This would save me having to link the cells across each of the 12
worksheets.
i hope this makes sense. I think i have confused myself!
Justin

 

[Tom Ogilvy]

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.

 

[Dave Peterson]

You got a response at your first post.

Hi
I am trying to consolidate information from 12 different workbooks
each with 12 worksheets (Jan-Dec) into one 'Group' Workbook (also
split by month). At present I am linking the cells as follow:
=[leisureclub1.xls]January!$F$16
=[leisureclub1.xls]January!$F$17
=[leisureclub1.xls]January!$F$18
=[leisureclub2.xls]January!$F$16
=[leisureclub2.xls]January!$F$17
and so on. this is very time consuming. Is there any way i can specify
the worksheet refernce from a cell in my consolidated spreadsheet i.e
=[leisureclub1.xls]"cell A1"!$F$16
=[leisureclub1.xls]"cell A1"!$F$17
This would save me having to link the cells across each of the 12
worksheets.
i hope this makes sense. I think i have confused myself!
Justin