Cell referencing

[Jorn]

Good Afternoon,

I know that within the spreadsheet itself, you can use VBA code to insert the value of one cell into another by using the following:

" & [A6] & "

What i would like to do is have these references within the footer of the document and thus far, it will not let me do that.

Does anyone know how i can do this? im using Excel 97....

Regards

John.

 

[Mat]

The only way I can figure it out, is to use VBA

Add in ThisWorkbook module this code:

Private Sub Workbook_BeforePrint(Cancel As Boolean) 

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

End Sub

each time you will print, it will look the value in cell A1 of the activesheet and put it as the left footer

Hope this helps,

 

[Jorn]

Cheers matey!

Have already tried to do some of the VBA referencing specific to the footer, but the biggest problems lies in that the text is to be taken from another spreadsheet, not the active one, and it spits the dummy if you reference that as you normally would.

Im content that the left footer wont let me do this- it runs into character restrictions as well so im not going to bother (this wee program is being designed to be used by an idiot and i think i should limit their power here and not let them touch the footer at all)- cheers for your help though. much appreciated.

Have a good one.

John.

 

[spiderrasmon]

that sounds good, but what if I wanted to use a relative reference (like "R1C1") and have it reference a cell below each page break on a given worksheet? how would that work?

 

[seand]

Hi There, this is my first post what would be the code then to reference one cell on the first page and have it print on the footers across all pages in the entire workbook? TIA :wink:

 

[Zack Barresse]

hi seand! welcome to the board!

try something like this...

Sub footer()

    Dim wk As Worksheet
    Dim sht As String
    Dim rng As String
    sht = ActiveSheet.Name
    rng = Sheets("Sheet1").Range("a1").Value
    On Error Resume Next
    
    For Each wk In ThisWorkbook.Worksheets
        wk.Activate
        ActiveSheet.PageSetup.LeftFooter = rng
    Next wk
    
    Sheets(sht).Select

End Sub

change as desired. worked for me

 

[seand]

WOW, that was quick thanks Zack, one last question how do I add a text line for example the footer is actually the "LoanID" which is referenced from the cell but I want to add the text Loan ID before the data that gets pulled from the cell TIA

 

[Zack Barresse]

so you want "Loan ID" as a footer, when the cell value is "LoanID", correct?

if this is the case, can you change the content of the cell? will it even stay the same, or be changing?

 

[seand]

Yes you have this correct, the content of the cell is time stamped with a static date/time when the file is created and does not get updated so it will not change, thanks.

 

[seand]

Hi Zack don't worry about the last message I got it it was

ActiveSheet.PageSetup.LeftFooter = "Loan ID" & Range("LoanID").Value

Thanks for your help :wink: