MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Reference linking to several worksheets automatically

Posted by Paul on July 26, 2001 8:45 AM

I have a question about linking data between different spreadsheets. I know how to get a cell in one spreadsheet to reference data in another spreadsheet. However, I want to automate that task for multiple spreadsheets.

For example let's say we have one spreadsheet called test.xls and another spreadsheet called 1.xls. I have data at A5 in 1.xls that I want to reference at A1 in test.xls. That much I can take care of. Now let's say I have data at A5 in 2.xls that I want to reference at A2 in test.xls, A5 in 3.xls refrenced in A3 in test.xls, etc. How can I automatically increment the file referenced in succeeding rows? I have about 900 files to reference, and instead of making the reference 900 times and then changing the file name, 900 times, is there a way to dynamincally change the file referenced given my file naming scheme.

I hope this makes sense. I was told it may be possible to do within a macro, but I have little experience with Excel macros.

Posted by faster on July 26, 2001 9:10 AM

Sub SelectSheet()
Dim i
For i = 1 To 900
'insert your code here using "i" as the variable
'you want to increment
Next i
End Sub