|
|
Hi,
in my tables I have some headers with merged cells. When printing,
these merged cells can span several pages, but the content is printed
only once, resulting in the spanned cells having no content in some
pages (i.e. the text fits into page 1, span continues to pages 2 and
3, but without text there).
I did try this also with Excel Pivot tables, which have similar
structure as my table. Surprisingly on Pivots it works well and
headers, which span across multiple pages, get their text reprinted on
each page. I also noted, that Pivot tables don't actually merge the
cells, they just draw frame around several cells, so that it looks
like a merged cell.
I got inspired by this and tried to create some formula, which will
duplicate the cell text in case that page break occurs just before the
cell. I created this macro (handles column spans only):
Function isColAfterBreak(ByRef x As Range)
Dim col As Long
Dim vpb As VPageBreaks
Dim breaksCount As Long
col = x.Column
breaksCount = x.Worksheet.VPageBreaks.Count
isColAfterBreak = False
For i = 1 To breaksCount
If x.Worksheet.VPageBreaks.Item(i).Location.Column = col Then
isColAfterBreak = True
Exit For
End If
Next i
End Function
Then I placed the text in the first cell and in other cells, which
should be "merged" with it I placed this formula:
=IF(isColAfterBreak(B1),$A1,"") (B1 is this cell, A1 it the cell
with the text)
By this, I was able to reach the desired outcome, but with several
problems:
- the recalculation has to be triggered manually
- the recalculation was very slow
- the macro got disabled by Excel security mechanisms after loading
this saved document (which is blocker)
Does anyone know any better solution how to have the cell text printed
on every page the cell spans across? I can't do it manually, since the
tables I have can be potentially very large.
Thanks for hints and suggestions in advance,
Michal
|
|
Hi,
Pivot tables have special feature to repeat tiltles. I'm not a 100% clear
on your problem, but here is one idea:
1. Replace the Merged cells with the Center Across Selection command. That
means unmerge the cells, highlight the range you originally merged and choose
Format, Cells, Alignment tab, Horizontal, Center Across Selection.
--
Thanks,
Shane Devenshire
"michal.dobisek[ at ]gmail.com" wrote:
[Quoted Text] > Hi,
>
> in my tables I have some headers with merged cells. When printing,
> these merged cells can span several pages, but the content is printed
> only once, resulting in the spanned cells having no content in some
> pages (i.e. the text fits into page 1, span continues to pages 2 and
> 3, but without text there).
>
> I did try this also with Excel Pivot tables, which have similar
> structure as my table. Surprisingly on Pivots it works well and
> headers, which span across multiple pages, get their text reprinted on
> each page. I also noted, that Pivot tables don't actually merge the
> cells, they just draw frame around several cells, so that it looks
> like a merged cell.
>
> I got inspired by this and tried to create some formula, which will
> duplicate the cell text in case that page break occurs just before the
> cell. I created this macro (handles column spans only):
>
> Function isColAfterBreak(ByRef x As Range)
> Dim col As Long
> Dim vpb As VPageBreaks
> Dim breaksCount As Long
>
> col = x.Column
> breaksCount = x.Worksheet.VPageBreaks.Count
>
> isColAfterBreak = False
> For i = 1 To breaksCount
> If x.Worksheet.VPageBreaks.Item(i).Location.Column = col Then
> isColAfterBreak = True
> Exit For
> End If
> Next i
>
> End Function
>
> Then I placed the text in the first cell and in other cells, which
> should be "merged" with it I placed this formula:
> =IF(isColAfterBreak(B1),$A1,"") (B1 is this cell, A1 it the cell
> with the text)
>
> By this, I was able to reach the desired outcome, but with several
> problems:
> - the recalculation has to be triggered manually
> - the recalculation was very slow
> - the macro got disabled by Excel security mechanisms after loading
> this saved document (which is blocker)
>
> Does anyone know any better solution how to have the cell text printed
> on every page the cell spans across? I can't do it manually, since the
> tables I have can be potentially very large.
>
> Thanks for hints and suggestions in advance,
>
> Michal
>
|
|
|