Insert cell value into footer

[nuficek]

Could anybody help me please why my code to insert cell value to the footer doesn't work?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = ThisWorkbook.Sheets("Sheet2")

            ws2.PageSetup.LeftFooter = format(ws1.Range("M3").Value, "dd-mmm-yyyy")
End Sub

 

[MARK858]

I tried your code as a normal sub and it works fine for me. Where have you placed the code?

 

[Michael M]

Where are you putting the code ?
Is M3 the correct location for the date ?

 

[nuficek]

I put it to the module. The problem is that the code doesn't start itself. So I think I've already found where the problem is. I copied the code under "Worksheet_Change" sub so when I change the value the code is triggered.

 

[MARK858]

I put it to the module.

What module? it should be in the ThisWorkBook module (that is if you leave as you have it written in the post).

 

[Michael M]

Are you firing the code by pressing the print button...it works fine for me as is !!
If you put it under the worksheet_change code ...there is your problem !! wrong module !

 

[nuficek]

Basically there are two options, 1) by print button, 2) via excel menu. So keep both variants working I had to put it under worksheet_change and it works now with no problem.

 

[MARK858]

1) by print button, 2) via excel menu

For both options the code as you have it in the thread should go in the ThisWorkbook module no need for it to be in a worksheet_change.

 

[nuficek]

For both options the code as you have it in the thread should go in the ThisWorkbook module no need for it to be in a worksheet_change.

I tried that but it doesn't work. That's why asked here.

 

[Michael M]

I tried that but it doesn't work

It actually does work in ThisWorkbook module, by any print selection method !
Are you calling it from worksheet_change event ?or other code ??