Cell referencing

Jorn

Board Regular
Joined
Jul 10, 2003
Messages
92
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Mat

Well-known Member
Joined
Sep 17, 2003
Messages
509
The only way I can figure it out, is to use VBA

Add in ThisWorkbook module this code:

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

Board Regular
Joined
Jul 10, 2003
Messages
92
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

New Member
Joined
Jan 27, 2004
Messages
9
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

New Member
Joined
Apr 13, 2004
Messages
15

ADVERTISEMENT

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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
hi seand! welcome to the board!

try something like this...

Code:
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

New Member
Joined
Apr 13, 2004
Messages
15

ADVERTISEMENT

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 :p
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Apr 13, 2004
Messages
15
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

New Member
Joined
Apr 13, 2004
Messages
15
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:
 

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top