I've created a Macro to copy data from a range in an open workbook
(data source) to an active workbook.
The Macro to do this is located in the source workbook and have
included the syntax below.

Because I've changed the filename the RngToCopy is no longer valid and
I'm hoping that someone can assist in helping me out with what the
syntax should be to prevent it from happening again when the Source
filename is changed.

The End User would open up the relevant workbook for the grant and
call the function. Thus, the grants workbook is the "active workbook"
but as you can see below, I've reference an actual workbook which I
believe isn't the best thing to do.


Ok, I've managed to figure Part A of the problem out, by using the
ThisWorkbook.name

---

Dim RngToCopy As Range
Dim DestCell As Range

Set RngToCopy = Workbooks("GRANT BALANCES - 2008.2009.xls").Worksheets
("Cover") _
.Range("report.cover.range")

Set DestCell = ActiveSheet.Range("B24")

---

It's now......

----

Dim RngToCopy As Range
Dim DestCell As Range
Dim ThisWorkBookName As String

MyWk = ThisWorkbook.Name

Set RngToCopy = Workbooks(MyWk).Worksheets("Cover") _
.Range("report.cover.range")

---

But now I've got this problem.....

---

ActiveCell.Formula = "=VLOOKUP(BProject,'[GRANT BALANCES -
2008.2009.xls]Grants Balance'!$A:$BG,58,FALSE)"
ActiveSheet.Range("B27:F27").Select

----

I would like to know how to get this to work......

Never mind... solved it......

ActiveCell.Formula = "=VLOOKUP(BProject,'[" & MyWk & "]Grants Balance'!
$A:$BG,58,FALSE)"

Even tho I've solved it, I've posted it anyway in the event in may be
of assistance to someene else.