Simple Macro Problem

J

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,557
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Maybe post the code so we can have a look at it ??
 
Upvote 0
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
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
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
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
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
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
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
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

C
  • Question
Macro Reverting to Old Setting???
Replies
1
Views
214
Joe4
Joe4
J
  • Question
Using VBA Power Query to import PDF tables
Replies
2
Views
3K
RoryA
RoryA
J
  • Question
Add blank row only applying to active worksheet
Replies
2
Views
375
Jaye16
J
S
  • Solved
Macro to filter each column for cells that appear empty, clear the contents and move to the next column for entire table
Replies
3
Views
132
sspatriots
S
D
  • Question
Applying dupicate Column Headers in an Excel Table
Replies
6
Views
392
awoohaw
awoohaw

Forum statistics

Threads
1,214,784
Messages
6,121,535
Members
449,037
Latest member
tmmotairi

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
Back
Top