Creating a Footer that contains the value of a cell on another Workbook

[Claire Jones]

Hi All

I am new to this forum (and to VBA) and am hoping you will be able to help me.

I am trying to find a way of putting the value of a cell from a specified sheet in one workbook into the footer of another sheet in another workbook.

The below works if you want the value of a cell in the same workbook but I can't figure out how to do if you want the value of a cell from a different workbook.

ActiveSheet.PageSetup.LeftFooter = Range("a1").Value

I tried the below but it did not work. Do you have any ideas?.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim ftr As String
Application.Workbooks("Book 1.xls").Worksheets("Sheet 1").Range("a1").Value = ftr
ActiveSheet.PageSetup.LeftFooter = ftr

End Sub

For the purpose of this lets say I want to put the value of range A1 on Sheet 1 of a workbook named "master" into the left footer of Sheet 1 in a workbook named "Specification 1".

Your help really would be most appreciated.

Many thanks, hope to hear from you soon.


Claire

 

[Andrew Poulsom]

Welcome to the Board.

Try:

ActiveSheet.PageSetup.LeftFooter = Workbooks("Book 1.xls").Worksheets("Sheet 1").Range("A1").Value

 

[Claire Jones]

Thank you. I will give that a go.


Claire

 

[Claire Jones]

Hi

I tried this as you said but it resultsed in an error saying subscript out of range.

Any ideas?

 

[Andrew Poulsom]

If you get that error you need to check that a workbook named "Book 1.xls" is open and that it contains a worksheet named "Sheet 1" (no quotes in both cases).

 

[Mark F]

Just a thought really

Is it definitely "Sheet 1" or could it be "Sheet1" ?

This is the type of thing thats gets me every time

Mark