Excel macro to insert carriage return

Question

In my research, I've encountered two possibilities for inserting a carriage return into a header in a VBA macro, using Chr(10) or Chr(13). I've even seen code posted by Allen Wyatt at excel.tips.com that seems to do exactly what I'm attempting, but where he asserts it works, I have yet to see success.

Here is the basic code I am attempting to execute:

With ActiveSheet.PageSetup
    .CenterHeader = "&F" & Chr(10) & "&A"
End With

There is other formatting I'm doing, but it all succeeds. This line only produces the filename in the header ("&F"), but no return and no tab name on the second line. It also doesn't fail; it just continues right through this line.

This macro was originally recorded by me in Excel 2010, which I then augmented with additional automation for page formatting. I'm still running it under Excel 2010, and it has never worked correctly on this particular line. Does anyone have knowledge of what might be transpiring here?

Edit: Here is the full code from the original macro recording and my edits.

Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1"
    .PrintTitleColumns = ""
    .LeftHeader = ""
    .CenterHeader = "&F" & vbCrLf & "&A"
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = "Printed &D"
    .LeftMargin = Application.InchesToPoints(0.7)
    .RightMargin = Application.InchesToPoints(0.7)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
    .PrintHeadings = False
    .PrintGridlines = True
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 100
    .PrintErrors = xlPrintErrorsDisplayed
    .OddAndEvenPagesHeaderFooter = False
    .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = True
    .EvenPage.LeftHeader.Text = ""
    .EvenPage.CenterHeader.Text = ""
    .EvenPage.RightHeader.Text = ""
    .EvenPage.LeftFooter.Text = ""
    .EvenPage.CenterFooter.Text = ""
    .EvenPage.RightFooter.Text = ""
    .FirstPage.LeftHeader.Text = ""
    .FirstPage.CenterHeader.Text = ""
    .FirstPage.RightHeader.Text = ""
    .FirstPage.LeftFooter.Text = ""
    .FirstPage.CenterFooter.Text = ""
    .FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True

works for me, are you sure you do not have something in the code that limits the size of the header so it does not show the second line? — Scott Craner
– Scott Craner, Commented Sep 28, 2017 at 19:40
I changed to vbCrLf,. Now the carriage return appears, but the tab name doesn't. I'm not aware of anything specifically limiting the header size, but I'll comb through it and see. — Mark
– Mark, Commented Sep 28, 2017 at 21:34
I didn't see anything, but I'll post the code here if someone want to take a look. I don't deny that this code change works (which I'll check the answer for), but it still isn't completing the entire line, which stymies me. — Mark
– Mark, Commented Sep 28, 2017 at 21:39
I thought I would follow up on this. I discovered that both methods worked if I removed some of the additional lines of code that weren't actually doing anything (they were simply there from the macro when it was first recorded). Once I eliminated things like the margin settings and empty header/footer lines, the carriage return and second line worked fine. — Mark
– Mark, Commented Dec 13, 2017 at 16:11

XLmatters · Accepted Answer · 2017-09-28 20:14:00Z

8

Agree with Patrick H. This code works ...

Sub header()
With ActiveSheet.PageSetup
    .CenterHeader = "&F" & vbCrLf & "&A"
End With
End Sub

answered Sep 28, 2017 at 20:14

XLmatters

3861 silver badge18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel macro to insert carriage return

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related