Excel VBA - read page number from cell content

[Hidden Dan]

Hello,

I'm not so well in VBA programming but I understand howit works.

I have a spreadsheet consisting of 15 different sheets. They should be integrated in a complex document from different sources. The final pdf document is paginated but the XLS sheets should provide the correct page number. Currently I add the number manually in sheets footer.

I wonder if the page number could be read from a cell value. The advantage would be that I don't have to select each individual sheet footer and type in its page number.


Would there be a VBA script that can read from cell A1 a number and print this in the right section of the footer ?

Say,

Sheet A : pagenumber = 10
Sheet B : pagenumber = 12
Sheet C : pagenumber = 15


Thanks,

Dan

 

[steve the fish]

For example:

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

 

[Hidden Dan]

Thanks,

But for some reason it will not work. But I don't get any error messages either.

Dan

 

[steve the fish]

Will not work doesnt give me much of a clue. Are you using it as a single line? If so what is in A1 of the activesheet at the time? If not paste all of the code you are using.

 

[Hidden Dan]

You are right

Sheet1
Cell A1 : 5


I created a module with this content :

Sub Pagenumber()

ActiveSheet.PageSetup.CenterFooter = Range("A1").Value
    
End Sub

 

[steve the fish]

Ok so you need to make sure you are on Sheet1 when that runs. This wouldnt care as long as you were within the correct workbook:

Sheets("Sheet1").PageSetup.CenterFooter = Sheets("Sheet1").Range("A1").Value

 

[Hidden Dan]

Unfortunately still nothing happens. I really don't understand why. Meanwhile I found a script based on your suggestion. It is said that it works and original programmer only asked for a small addition.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.LeftFooter = "&""Arial,Regular""&6" & "Aisle  " & Range("A2").Value

ActiveSheet.PageSetup.CenterFooter = "&""Arial,Regular""&6" & "Floor " & Range("B2").Value

ActiveSheet.PageSetup.RightFooter = "&""Arial,Regular""&6" & Range("C2").Value

End Sub

So I tried this script in module1 but still no result. As far as I know it can't be due to the fact that I use Windows / Excel in a different language.

 

[steve the fish]

That code should be in the 'ThisWorkbook' module.

 

[Hidden Dan]

I tried but it won't work.

I don't understand why it doesn't as all my other VBA scripts do work.

Maybe I overlook something.


Thanks anyway, Dan

 

[Hidden Dan]

Got it working

On every page

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call SetFooter
End If
End Sub

And in a Module

Sub SetFooter()
ActiveSheet.PageSetup.LeftFooter = Range("A1").Text
End Sub