Hi,

How can I use the active cell address as a part of a macro.

I want to use the column heading (which is a part of a file name) to
copy certain fields from the corresonding file into the column where
the active cell is at.

This is what I've got:

Sub test()

Sht = "1) Other Income"

With Worksheets(Sht)

FR = 14 'FirstRow
LR = 25 'LastRow
COL = "W" 'Column
Cno = 11
Cnm = Cno + 1

.Range(COL & FR & ":" & COL & LR).Formula = "=+'J:\Year End\Year
End 2008\Disclosure Packs\[" & Range(COL & Cnm).Value & " - " &
Range(COL & Cno).Value & " FY08 REPORTING PACK.xls]" & Sht & "'!F" &
FR

End With

End Sub

Cheers,

Harold