capture WEEKNUM & place in page setup

[Dan]

Thanks in advance. I have a spreadsheet where I setup the
page to break every Saturday (thanks Dave P.). So each
page contains Mon to Sat. My spreadsheet dates begin in
column A2. I would like to print the WEEKNUM of each page
on the page setup footer based on the first date of each
page. How would I do this?

I am not sure if I have to capture the WEEKNUM when I set
the Page Breaks, code below, and then reference the
capture on page setup or do it all on the page setup
coding.


Sub mcrAddBreaks()

Worksheets("Final").Activate
Worksheets("Final").Range("A1").Select
Dim StartRow As Long
Dim FinalRow As Long
Dim FVal As Date
Dim FirstVal As Long
Dim NVal As Date
Dim NextVal As Long
Dim i As Integer

StartRow = 2
FinalRow = Range("A65536").End(xlUp).Row

For i = StartRow To FinalRow

FVal = Cells(i, 1).Value
NVal = Cells(i + 1, 1).Value
FirstVal = Weekday(FVal)
NextVal = Weekday(NVal)
If (FirstVal = 7) And (NextVal = 2) Then
ActiveSheet.HPageBreaks.Add before:=Cells(i +
1, 1)
End If
FVal = NVal
Next i

End Sub

 

[David McRitchie]

Hi Dan,
I think this is the question you tried to point to in another post.
Best to keep to the same thread rather than starting up another
thread and expect people to look back for a previous one.
Date and time something was posted isn't much help, well
at least not the time because we'd have to ignore the hour and
look for the minutes and hopefully the data would be the same.

Anyway I would suggest you abandon this last request, such
facility is not builtin into Excel or printing -- don't hold your
breath that Excel would make such things simpler in the future.

In order to do what you ask you would have to print each page
separately by printing one page, change the value, print one page,
change the value. It can be done with a macro, but I'd hardly say
that it is all that straight forward. If you absolutely must have it
then see some of the newsgroup postings. Any change that you make
must not change the number of lines on a page. Here are some
links.

Restart the printer to print each page with a customized title or footer per page
Headings for Columns, and Page Numbering
http://www.mvps.org/dmcritchie/excel/headcols.htm#restart

Since you are setting page breaks for each week you could put
the information into a specific cell, perhaps some offset
form where the page break is being set so that you have it on
the page itself.

 

[Dan]

Thanks David for the information. I will not pursue this request because it
just complicates my procedure and goal. Also, thanks for the tread advice. I
was not sure that if I posted to my original that , since it was a few days
old, it would resurface to get attention. I will check out the info on your
link and site...always looking to learn from the pros. Thanks again, Dan

Hi Dan,
I think this is the question you tried to point to in another post.
Best to keep to the same thread rather than starting up another
thread and expect people to look back for a previous one.
Date and time something was posted isn't much help, well
at least not the time because we'd have to ignore the hour and
look for the minutes and hopefully the data would be the same.

Anyway I would suggest you abandon this last request, such
facility is not builtin into Excel or printing -- don't hold your
breath that Excel would make such things simpler in the future.

In order to do what you ask you would have to print each page
separately by printing one page, change the value, print one page,
change the value. It can be done with a macro, but I'd hardly say
that it is all that straight forward. If you absolutely must have it
then see some of the newsgroup postings. Any change that you make
must not change the number of lines on a page. Here are some
links.

Restart the printer to print each page with a customized title or footer per page
Headings for Columns, and Page Numbering
http://www.mvps.org/dmcritchie/excel/headcols.htm#restart

Since you are setting page breaks for each week you could put
the information into a specific cell, perhaps some offset
form where the page break is being set so that you have it on
the page itself.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I am not sure if I have to capture the WEEKNUM when I set
the Page Breaks, code below, and then reference the
capture on page setup or do it all on the page setup
coding.


Sub mcrAddBreaks()

Worksheets("Final").Activate
Worksheets("Final").Range("A1").Select
Dim StartRow As Long
Dim FinalRow As Long
Dim FVal As Date
Dim FirstVal As Long
Dim NVal As Date
Dim NextVal As Long
Dim i As Integer

StartRow = 2
FinalRow = Range("A65536").End(xlUp).Row

For i = StartRow To FinalRow

FVal = Cells(i, 1).Value
NVal = Cells(i + 1, 1).Value
FirstVal = Weekday(FVal)
NextVal = Weekday(NVal)
If (FirstVal = 7) And (NextVal = 2) Then
ActiveSheet.HPageBreaks.Add before:=Cells(i +
1, 1)
End If
FVal = NVal
Next i

End Sub