Furmula Question

[kluitna]

A quick question on setting up variables in a formula.

I have the current formula:

ActiveCell.FormulaR1C1 = "='" & strfile & "'!R21C4"

I am using it to link several workbooks together. That have several worksheets in them.

"strfile" is the workbook file name, This works just fine. My problem is I can't seem to get the formula on the next pass throughthe loop to look at the next worksheet in "strfile". Does anyone know the syntax to look at the same file and the next sheet in a file.?

Any ideas are much appreciated.

 

[kluitna]

Hey Al, here is the working code, Again thank you this moves me way ahead here.

Sub test()
Dim Source As String
Dim Path As String
Dim NumSheets As Long
Dim SheetName As String
Dim strfile As Workbook
Path = Worksheets("List").Cells(1, 1)
For i = 0 To 500
Source = Worksheets("list").Cells(2 + i, 1)
If Source = blank Then End
ChDir Path
Workbooks.Open FileName:=Source
For h = 1 To 30
Windows("Estimate Summary.xls").Activate
Worksheets("ITEMS").Select
Worksheets("ITEMS").Cells(6 + i, 1 + h).Select
Set strfile = Workbooks(Source)
ActiveCell.Value = strfile.Worksheets(h).Range("d21")
Next h
Workbooks(Source).Close
Next
End Sub


As you can see from the code I get the file nomes for the workbooks from a list worksheet in the workbook I am working with, works really slick. No thatI have figured out the hard part this won't be bad at all.

Again thank you very much