Simple Macro Problem

J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,508
Office Version
  1. 365
Platform
  1. Windows
I recorded a macro to format an imported spreadsheet. The macro runs perfectly except for the header and footer. Instead of the header saying "ready board," the header says "ready boa" after the code runs. For the left footer, instead of having the date and time on the left, it just has the date; right footer says "1 of" instead of page 1 of 12. I havce been working on this for hours and cannot figure out why the headers and footers are fine until the code runs.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Michael M

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,607
Office Version
  1. 365
  2. 2019
  3. 2013
  4. 2007
Platform
  1. Windows
Maybe post the code so we can have a look at it ??
 
Upvote 0
J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,508
Office Version
  1. 365
Platform
  1. Windows
Of course!

Sub fghfghdgsdrterttytyugmnfsgsgfsdf()
'
' Format Macro
'
Sheets("Make Ready Board").Select
Cells.Select
Selection.Font.Size = 10
Selection.Font.Size = 9
Selection.Font.Size = 8
Cells.EntireColumn.AutoFit
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("H:K").Select
Selection.EntireColumn.Hidden = True
Columns("N:N").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=13
Columns("Z:AA").Select
Selection.EntireColumn.Hidden = True
Columns("AD:AE").Select
Selection.EntireColumn.Hidden = True
Columns("L:AB").Select
Range("AB1").Activate
Selection.ColumnWidth = 10.71
Selection.ColumnWidth = 8
Selection.ColumnWidth = 7.43
Selection.ColumnWidth = 7.57
Selection.ColumnWidth = 7.71
Selection.ColumnWidth = 8
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=-18
Range("M6").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&B Make Ready Board &B" & Chr(13) & "Prime Group" & Chr(13) & "Park La Brea"
.RightHeader = ""
.LeftFooter = "&D &T"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.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 = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.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
ActiveWindow.SmallScroll Down:=-9
Columns("Z:AA").Select
Range("AA1").Activate
Selection.EntireColumn.Hidden = True
Columns("E:G").Select
Range("G1").Activate
Selection.ColumnWidth = 8.14
Selection.ColumnWidth = 8
Columns("B:B").ColumnWidth = 0
Columns("A:A").ColumnWidth = 7.14
Range("P4").Select
Columns("AC:AC").ColumnWidth = 15.43
Columns("AC:AC").ColumnWidth = 14.86
Columns("AC:AC").ColumnWidth = 14.29
Columns("L:AB").Select
Selection.ColumnWidth = 7.71
Selection.ColumnWidth = 7.57
Range("O5").Select
Columns("C:C").ColumnWidth = 9.29
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Columns("AA:AA").Select
Selection.EntireColumn.Hidden = True
Range("O6").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&B Make Ready Board &B" & Chr(13) & "Prime Group" & Chr(13) & "Park La Brea"
.RightHeader = ""
.LeftFooter = "&D &T"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.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 = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 90
.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
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&B Make Ready Board &B" & Chr(13) & "Prime Group" & Chr(13) & "Park La Brea"
.RightHeader = ""
.LeftFooter = "&D &T"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.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 = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.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
End Sub
 
Upvote 0
Michael M

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,607
Office Version
  1. 365
  2. 2019
  3. 2013
  4. 2007
Platform
  1. Windows
It works fine for me !
Are you redoing the Page Setup 3 times for any particular reason, or was that just the way you recorded the macro ??
 
Upvote 0
Michael M

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,607
Office Version
  1. 365
  2. 2019
  3. 2013
  4. 2007
Platform
  1. Windows
Shortened it up some...still works OK for me
Code: 
Sub fghfghdgsdrterttytyugmnfsgsgfsdf()
'
' Format Macro
'
With Sheets("Make Ready Board")
.Cells.Font.Size = 8
Cells.EntireColumn.AutoFit
Columns("B:B").EntireColumn.Hidden = True
Columns("D:D").EntireColumn.Hidden = True
Columns("H:K").EntireColumn.Hidden = True
Columns("N:N").EntireColumn.Hidden = True
Columns("Z:AA").EntireColumn.Hidden = True
Columns("AD:AE").EntireColumn.Hidden = True
Columns("L:AB").ColumnWidth = 8
With Rows("1:1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.ReadingOrder = xlContext
End With
Columns("E:G").ColumnWidth = 8
Columns("B:B").ColumnWidth = 0
Columns("A:A").ColumnWidth = 7.14
Columns("AC:AC").ColumnWidth = 14.29
Columns("L:AB").ColumnWidth = 7.57
Columns("C:C").ColumnWidth = 9.29
Columns("Z:AA").EntireColumn.Hidden = True
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&B Make Ready Board &B" & Chr(13) & "Prime Group" & Chr(13) & "Park La Brea"
.RightHeader = ""
.LeftFooter = "&D &T"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.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)
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.Orientation = xlLandscape
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 85
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
End With
End With
Application.PrintCommunication = True
End Sub
 
Upvote 0
J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,508
Office Version
  1. 365
Platform
  1. Windows
Yes, I just recorded it that way for no particular reason.

I ran your code but I am still having the same problem: left footer says 9/10/2014 instead of 9/10/2014 7:17pm and the right footer still says 1 of instead of page 1 of 12. The header is also still clipping off a few letters on the end. I cannot figure this out.
 
Upvote 0
Michael M

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,607
Office Version
  1. 365
  2. 2019
  3. 2013
  4. 2007
Platform
  1. Windows
Did a bit of a search and found a suggestion that Print Communications needs to be turned off.
It doesn't appear to make any difference to me, but might for you.

Also, is the problem visible in Print Preview or only on the printed page ??
Code: 
Sub fghfghdgsdrterttytyugmnfsgsgfsdf()
With Sheets("Make Ready Board")
    .Cells.Font.Size = 8
    Cells.EntireColumn.AutoFit
    Columns("B:B").EntireColumn.Hidden = True
    Columns("D:D").EntireColumn.Hidden = True
    Columns("H:K").EntireColumn.Hidden = True
    Columns("N:N").EntireColumn.Hidden = True
    Columns("Z:AA").EntireColumn.Hidden = True
    Columns("AD:AE").EntireColumn.Hidden = True
    Columns("L:AB").ColumnWidth = 8
Application.PrintCommunication = False
With Rows("1:1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .ReadingOrder = xlContext
End With
Columns("E:G").ColumnWidth = 8
Columns("B:B").ColumnWidth = 0
Columns("A:A").ColumnWidth = 7.14
Columns("AC:AC").ColumnWidth = 14.29
Columns("L:AB").ColumnWidth = 7.57
Columns("C:C").ColumnWidth = 9.29
With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1"
    .PrintTitleColumns = ""
    .LeftHeader = ""
    .CenterHeader = "&B Make Ready Board &B" & Chr(13) & "Prime Group" & Chr(13) & "Park La Brea"
    .RightHeader = ""
    .LeftFooter = "&D &T"
    .CenterFooter = ""
    .RightFooter = "Page &P of &N"
    .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)
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .Orientation = xlLandscape
    .PaperSize = xlPaperLegal
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = 85
    .PrintErrors = xlPrintErrorsDisplayed
    .OddAndEvenPagesHeaderFooter = False
    .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = True
End With
End With
End Sub
 
Upvote 0
J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,508
Office Version
  1. 365
Platform
  1. Windows
Thank you for taking the time to help me!

Yes, the problem is visible in print preview but only occurs after I run my macro to format.

Your new code worked not all of the columns fit on one page anymore and it left out three changes I recorded in the first macro. Columns should only extend to T:

- Letter changed to legal
- Scale changed from 100% to90% or 85%
- Margins changed to 0 for left and right

So your new code works but it gives me a report that is 24 pages. I am trying to make all columns fit on one page as well as make the report as large and readable as possible while also trying to keep the number of pages somewhere in the teens, if possible.

Would it help if I posted my workbook so you can see what the final workbook should look like minus the headers and footers not working?
 
Upvote 0
Michael M

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,607
Office Version
  1. 365
  2. 2019
  3. 2013
  4. 2007
Platform
  1. Windows
What were the 3 things left out ?
You say the columns should only extend to Col "T", yet your original code goes to Col "AC"
Your original code sets up for Legal paper each time !
The activesheet in the original code was set to 85% zoom
The code I provided sets the L & R margins at 0.7

As I stated in the 2nd post, your original code and my code worked fine for me
 
Upvote 0
J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,508
Office Version
  1. 365
Platform
  1. Windows
My apologies - I did not assign the macro when I altered the code you sent me so it executed the original code.

This works great except that the borders of the cells are gone and wrap text is no longer active as some text is hidden.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dr. Demento
  • Question
Loop thru PageSetup.headers/footers
Replies
1
Views
287
Dr. Demento
Dr. Demento
T
  • Question
VBA Different first page/odd page header/footer
Replies
1
Views
1K
John_w
J
E
  • Question
VBA error saving a new sheet. "That name is already taken"
Replies
2
Views
211
HaHoBe
HaHoBe
M
  • Solved
Copying a workbook sheet to the bottom of another workbook sheet
Replies
3
Views
284
Joe4
Joe4
D
  • Locked
  • Solved
Using Loop to sum based on content in adjacent column then placing that sum.
Replies
3
Views
354
Fluff
Fluff

Forum statistics

Threads
1,191,576
Messages
5,987,386
Members
440,095
Latest member
yanaungmyint

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top