• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

Excel Macro Custom Header MIssing

I have created a macro which sorts data and formats worksheets.

Part of the format inputs a logo at the top of the page and a date stamp at the bottom.

However, after running the macro the footer shows, but the header doesn't.

I have attached the macro - can anyone advise why this may happen?

Rows("1:5").Select
    Range("A2").Activate
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.RowHeight = 25
    Rows("1:1").Select
    Selection.RowHeight = 35
    Selection.RowHeight = 40
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
        "\\NCIS01\RedirectedFolders\philipb\My Documents\NCi Projects\Document Management\Images\nci_logo.jpg"
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Height = 42.75
        .Width = 64.5
    End With
    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 = "&""Arial,Bold""&U&F"
        .RightHeader = "&G"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&""Arial,Italic""&8&D"
        .LeftMargin = Application.InchesToPoints(0.196850393700787)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.984251968503937)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.196850393700787)
        .FooterMargin = Application.InchesToPoints(0.196850393700787)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 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
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    Columns("B:B").Select
    Selection.ColumnWidth = 9.43
    Columns("C:C").Select
    Selection.ColumnWidth = 24.43
    ActiveWindow.SmallScroll Down:=435
    Selection.ColumnWidth = 28.14
    Columns("D:D").Select
    Range("D436").Activate
    Selection.ColumnWidth = 22
    Columns("C:C").Select
    Range("C451").Activate
    Selection.ColumnWidth = 24.57
    Cells.Select
    Range("B451").Activate
    Selection.RowHeight = 40
    Columns("C:D").Select
    Selection.ColumnWidth = 22
    Columns("E:E").Select
    Selection.ColumnWidth = 23.29
    Selection.ColumnWidth = 17.86
    Columns("F:F").Select
    Selection.ColumnWidth = 17.14
    Columns("G:H").Select
    Selection.ColumnWidth = 11.57
    Columns("I:L").Select
    Selection.ColumnWidth = 11.43
    Range("B1").Select
    Sheets("...").Select
    Sheets("...").Name = "Base"
    Sheets("Base").Select
    Sheets("Base").Copy After:=Sheets(1)
    Sheets("Base (2)").Select
    Sheets("Base (2)").Name = "Master"
    Sheets("Master").Select
    Selection.AutoFilter
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(2)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH COR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Elliott, Susan", "Melrose, Alison"), Operator _
        :=xlFilterValues
    Sheets("CH COR").Select
    Sheets("CH COR").Copy After:=Sheets(3)
    Sheets("CH COR (2)").Select
    Sheets("CH COR (2)").Name = "CH Elliott, Susan"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
    "ELLIOTT, Susan"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH COR").Select
    Sheets("CH COR").Copy After:=Sheets(4)
    Sheets("CH COR (2)").Select
    Sheets("CH COR (2)").Name = "CH MELROSE, Alison"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
    "MELROSE, Alison"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(5)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH - MTR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Baber, David", "Bailey, Michael", "Davis, Colin", "Pozniak, Samantha", "Brown, Peter", "Brown, Janet", "Fenwick, Ray", "Tait, Debbie", "Dodsworth, Peter"), Operator _
        :=xlFilterValues
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(6)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - BABER, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Baber, David"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(7)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - BAILEY, Michael"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Bailey, Michael"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(8)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - DAVIS, Colin"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Davis, Colin"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(9)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - POZNIAK, Samantha"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Pozniak, Samantha"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(10)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - WINLOW, Emma"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "WINLOW, Emma"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(11)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH - SME"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "McGill, Ken", _
        "Yeaman, Lynne", "Milburn, Sarah", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(12)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BELGIAN, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Belgian, David"
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(13)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BELGIAN, Philip"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Belgian, Philip"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - BELGIAN, David").Select
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(14)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BOYLE, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Boyle, Lynne"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(15)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - HIFTLE, Les"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Hiftle, Les"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(16)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - MCGILL, Ken"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "McGill, Ken"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(17)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - MILBURN, Sarah"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "McGill, Ken"
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(18)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - YEAMAN, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Yeaman, Lynne"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MILBURN, Sarah").Select
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(19)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - COR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Melrose, Alison"
    Sheets("PH - COR").Select
    Sheets("PH - COR").Copy After:=Sheets(20)
    Sheets("PH - COR (2)").Select
    Sheets("PH - COR (2)").Name = "PH - ELLIOTT, Susan"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - COR").Select
    Sheets("PH - COR").Copy After:=Sheets(21)
    Sheets("PH - COR (2)").Select
    Sheets("PH - COR (2)").Name = "PH - MELROSE, Alison"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(22)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - MTR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Baber, David", "Bailey, Michael", "Davis, Colin", "Dodsworth, Peter", _
        "Pozniak, Samantha", "Brown, Peter", "Brown, Janet", "Fenwick, Ray", "Tait, Debbie", "Dodsworth, Peter"), Operator:=xlFilterValues
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(23)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - BABER, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "=Baber, David", Operator:=xlOr, Criteria2:="=Davis, Colin"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(24)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - BAILEY, Michael"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Bailey, Michael"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(25)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - DAVIS, Colin"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Davis, Colin"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(26)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - POZNIAK, Samantha"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Pozniak, Samantha"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(27)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - WINLOW, Emma"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Winlow, Emma"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(28)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - SME"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "Lamb, Andrew", _
        "McGill, Ken", "Milburn, Sarah", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Sheets("PH - SME").Select
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "Lamb, Andrew", _
        "McGill, Ken", "Milburn, Sarah", "Yeaman, Lynne", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Columns("H:H").Select
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(29)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BELGIAN, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Belgian, David"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(30)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BELGIAN, Philip"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Belgian, Philip"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(31)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BOYLE, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Boyle, Lynne"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(32)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - HIFTLE, Les"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Hiftle, Les"
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(33)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - MCGILL, Ken"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "McGill, Ken"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(34)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - MILBURN, Sarah"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Milburn, Sarah"
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(35)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - YEAMAN, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Yeaman, Lynne"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Sheets("Master").Copy Before:=Sheets(29)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - PPPRET"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Hurst, Sue", "Potts, Lesley Ann", "Shaw, Tracy", "Talbot-Jones, Richard", _
        "Watson, Nichola", "Bull, Lucy", "Herdman, Richard", "Hodgson, Irene", "Taylor, Helen"), Operator:=xlFilterValues
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SmallScroll ToRight:=-2
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Range("B1").Select
End Sub
0
BelgianPhilip
Asked:
BelgianPhilip
  • 2
1 Solution
 
Ardhendu SarangiSr. Project ManagerCommented:
First check if the path and the logo is accessible by excel. Try moving the logo to a local drive instead and see if that works?

if that doesn't work, please attach your logo "nci_logo.jpg" here. Can you also post your sample sheet and the complete macro? please delete/dummy up any sensitive data and attach it here.

thanks,
Ardhendu
0
 
Nico BontenbalCommented:
It's this bit that does the headers:
ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
        "\\NCIS01\RedirectedFolders\philipb\My Documents\NCi Projects\Document Management\Images\nci_logo.jpg"
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Height = 42.75
        .Width = 64.5
    End With
    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 = "&""Arial,Bold""&U&F"
        .RightHeader = "&G"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&""Arial,Italic""&8&D"
        .LeftMargin = Application.InchesToPoints(0.196850393700787)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.984251968503937)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.196850393700787)
        .FooterMargin = Application.InchesToPoints(0.196850393700787)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 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

Open in new window

When I run it in Excel 2007 it works fine. But in 2010 it doesn't change the headers. I changed the piece of code above to:
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .PrintArea = ""
        .RightHeaderPicture.Filename = _
        "\\NCIS01\RedirectedFolders\philipb\My Documents\NCi Projects\Document Management\Images\nci_logo.jpg"
        With .RightHeaderPicture
            .Height = 42.75
            .Width = 64.5
        End With
        .LeftHeader = ""
        .CenterHeader = "&""Arial,Bold""&U&F"
        .RightHeader = "&G"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&""Arial,Italic""&8&D"
        .LeftMargin = Application.InchesToPoints(0.196850393700787)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.984251968503937)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.196850393700787)
        .FooterMargin = Application.InchesToPoints(0.196850393700787)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 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

Open in new window

And now it seems to work ok.
0
 
BelgianPhilipAuthor Commented:
Hi pari123,

The logo doesn't work from a local path.

Full macro below and logo/sheet attached.

Sub RenewalList()
'
' RenewalList Macro
'

'
    Rows("1:5").Select
    Range("A2").Activate
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.RowHeight = 25
    Rows("1:1").Select
    Selection.RowHeight = 35
    Selection.RowHeight = 40
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
        "C:\Users\nci_logo.jpg"
    With ActiveSheet.PageSetup.RightHeaderPicture
        .Height = 42.75
        .Width = 64.5
    End With
    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 = "&""Arial,Bold""&U&F"
        .RightHeader = "&G"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&""Arial,Italic""&8&D"
        .LeftMargin = Application.InchesToPoints(0.196850393700787)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.984251968503937)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.196850393700787)
        .FooterMargin = Application.InchesToPoints(0.196850393700787)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 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
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    Columns("B:B").Select
    Selection.ColumnWidth = 9.43
    Columns("C:C").Select
    Selection.ColumnWidth = 24.43
    ActiveWindow.SmallScroll Down:=435
    Selection.ColumnWidth = 28.14
    Columns("D:D").Select
    Range("D436").Activate
    Selection.ColumnWidth = 22
    Columns("C:C").Select
    Range("C451").Activate
    Selection.ColumnWidth = 24.57
    Cells.Select
    Range("B451").Activate
    Selection.RowHeight = 40
    Columns("C:D").Select
    Selection.ColumnWidth = 22
    Columns("E:E").Select
    Selection.ColumnWidth = 23.29
    Selection.ColumnWidth = 17.86
    Columns("F:F").Select
    Selection.ColumnWidth = 17.14
    Columns("G:H").Select
    Selection.ColumnWidth = 11.57
    Columns("I:L").Select
    Selection.ColumnWidth = 11.43
    Range("B1").Select
    Sheets("...").Select
    Sheets("...").Name = "Base"
    Sheets("Base").Select
    Sheets("Base").Copy After:=Sheets(1)
    Sheets("Base (2)").Select
    Sheets("Base (2)").Name = "Master"
    Sheets("Master").Select
    Selection.AutoFilter
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(2)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH COR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Elliott, Susan", "Melrose, Alison"), Operator _
        :=xlFilterValues
    Sheets("CH COR").Select
    Sheets("CH COR").Copy After:=Sheets(3)
    Sheets("CH COR (2)").Select
    Sheets("CH COR (2)").Name = "CH Elliott, Susan"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
    "ELLIOTT, Susan"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH COR").Select
    Sheets("CH COR").Copy After:=Sheets(4)
    Sheets("CH COR (2)").Select
    Sheets("CH COR (2)").Name = "CH MELROSE, Alison"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
    "MELROSE, Alison"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(5)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH - MTR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Baber, David", "Bailey, Michael", "Davis, Colin", "Pozniak, Samantha", "Brown, Peter", "Brown, Janet", "Fenwick, Ray", "Tait, Debbie", "Dodsworth, Peter", "Connell, Linda", "Belgian-Cooper, Helen"), Operator _
        :=xlFilterValues
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(6)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - BABER, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Baber, David"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(7)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - BAILEY, Michael"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Bailey, Michael"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(8)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - DAVIS, Colin"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Davis, Colin"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(9)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - POZNIAK, Samantha"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Pozniak, Samantha"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(10)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - WINLOW, Emma"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "WINLOW, Emma"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(11)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH - SME"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "McGill, Ken", _
        "Yeaman, Lynne", "Milburn, Sarah", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(12)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BELGIAN, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Belgian, David"
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(13)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BELGIAN, Philip"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Belgian, Philip"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - BELGIAN, David").Select
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(14)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BOYLE, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Boyle, Lynne"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(15)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - HIFTLE, Les"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Hiftle, Les"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(16)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - MCGILL, Ken"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "McGill, Ken"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(17)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - MILBURN, Sarah"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "McGill, Ken"
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(18)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - YEAMAN, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Yeaman, Lynne"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MILBURN, Sarah").Select
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(19)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - COR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Melrose, Alison"
    Sheets("PH - COR").Select
    Sheets("PH - COR").Copy After:=Sheets(20)
    Sheets("PH - COR (2)").Select
    Sheets("PH - COR (2)").Name = "PH - ELLIOTT, Susan"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - COR").Select
    Sheets("PH - COR").Copy After:=Sheets(21)
    Sheets("PH - COR (2)").Select
    Sheets("PH - COR (2)").Name = "PH - MELROSE, Alison"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(22)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - MTR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Baber, David", "Bailey, Michael", "Davis, Colin", "Dodsworth, Peter", _
        "Pozniak, Samantha", "Brown, Peter", "Brown, Janet", "Fenwick, Ray", "Tait, Debbie", "Connell, Linda", "Belgian-Cooper, Helen"), Operator:=xlFilterValues
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(23)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - BABER, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "=Baber, David", Operator:=xlOr, Criteria2:="=Davis, Colin"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(24)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - BAILEY, Michael"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Bailey, Michael"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(25)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - DAVIS, Colin"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Davis, Colin"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(26)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - POZNIAK, Samantha"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Pozniak, Samantha"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(27)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - WINLOW, Emma"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Winlow, Emma"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(28)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - SME"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "Lamb, Andrew", _
        "McGill, Ken", "Milburn, Sarah", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Sheets("PH - SME").Select
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "Lamb, Andrew", _
        "McGill, Ken", "Milburn, Sarah", "Yeaman, Lynne", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Columns("H:H").Select
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(29)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BELGIAN, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Belgian, David"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(30)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BELGIAN, Philip"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Belgian, Philip"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(31)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BOYLE, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Boyle, Lynne"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(32)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - HIFTLE, Les"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Hiftle, Les"
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(33)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - MCGILL, Ken"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "McGill, Ken"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(34)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - MILBURN, Sarah"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Milburn, Sarah"
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(35)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - YEAMAN, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Yeaman, Lynne"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Sheets("Master").Copy Before:=Sheets(29)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - PPPRET"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Hurst, Sue", "Potts, Lesley Ann", "Shaw, Tracy", "Talbot-Jones, Richard", _
        "Watson, Nichola", "Bull, Lucy", "Herdman, Richard", "Hodgson, Irene", "Taylor, Helen"), Operator:=xlFilterValues
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SmallScroll ToRight:=-2
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Range("B1").Select
End Sub

nci-logo.jpg
Book2.xlsx
0
 
Nico BontenbalCommented:
Try to change your macro to this:
Sub RenewalList()
'
' RenewalList Macro
'

'
    Rows("1:5").Select
    Range("A2").Activate
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.RowHeight = 25
    Rows("1:1").Select
    Selection.RowHeight = 35
    Selection.RowHeight = 40
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With Selection
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    With ActiveSheet.PageSetup
        .PrintArea = ""
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .RightHeaderPicture.Filename = "g:\data\ee\nci-logo.jpg"
        With ActiveSheet.PageSetup.RightHeaderPicture
            .Height = 42.75
            .Width = 64.5
        End With
        .LeftHeader = ""
        .CenterHeader = "&""Arial,Bold""&U&F"
        .RightHeader = "&G"
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&""Arial,Italic""&8&D"
        .LeftMargin = Application.InchesToPoints(0.196850393700787)
        .RightMargin = Application.InchesToPoints(0.196850393700787)
        .TopMargin = Application.InchesToPoints(0.984251968503937)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.196850393700787)
        .FooterMargin = Application.InchesToPoints(0.196850393700787)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 300
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 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
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    Columns("B:B").Select
    Selection.ColumnWidth = 9.43
    Columns("C:C").Select
    Selection.ColumnWidth = 24.43
    ActiveWindow.SmallScroll Down:=435
    Selection.ColumnWidth = 28.14
    Columns("D:D").Select
    Range("D436").Activate
    Selection.ColumnWidth = 22
    Columns("C:C").Select
    Range("C451").Activate
    Selection.ColumnWidth = 24.57
    Cells.Select
    Range("B451").Activate
    Selection.RowHeight = 40
    Columns("C:D").Select
    Selection.ColumnWidth = 22
    Columns("E:E").Select
    Selection.ColumnWidth = 23.29
    Selection.ColumnWidth = 17.86
    Columns("F:F").Select
    Selection.ColumnWidth = 17.14
    Columns("G:H").Select
    Selection.ColumnWidth = 11.57
    Columns("I:L").Select
    Selection.ColumnWidth = 11.43
    Range("B1").Select
    Sheets("...").Select
    Sheets("...").Name = "Base"
    Sheets("Base").Select
    Sheets("Base").Copy After:=Sheets(1)
    Sheets("Base (2)").Select
    Sheets("Base (2)").Name = "Master"
    Sheets("Master").Select
    Selection.AutoFilter
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(2)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH COR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Elliott, Susan", "Melrose, Alison"), Operator _
        :=xlFilterValues
    Sheets("CH COR").Select
    Sheets("CH COR").Copy After:=Sheets(3)
    Sheets("CH COR (2)").Select
    Sheets("CH COR (2)").Name = "CH Elliott, Susan"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
    "ELLIOTT, Susan"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH COR").Select
    Sheets("CH COR").Copy After:=Sheets(4)
    Sheets("CH COR (2)").Select
    Sheets("CH COR (2)").Name = "CH MELROSE, Alison"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
    "MELROSE, Alison"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(5)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH - MTR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Baber, David", "Bailey, Michael", "Davis, Colin", "Pozniak, Samantha", "Brown, Peter", "Brown, Janet", "Fenwick, Ray", "Tait, Debbie", "Dodsworth, Peter"), Operator _
        :=xlFilterValues
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(6)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - BABER, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Baber, David"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(7)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - BAILEY, Michael"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Bailey, Michael"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(8)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - DAVIS, Colin"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Davis, Colin"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(9)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - POZNIAK, Samantha"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Pozniak, Samantha"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MTR").Select
    Sheets("CH - MTR").Copy After:=Sheets(10)
    Sheets("CH - MTR (2)").Select
    Sheets("CH - MTR (2)").Name = "CH - WINLOW, Emma"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "WINLOW, Emma"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(11)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "CH - SME"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "McGill, Ken", _
        "Yeaman, Lynne", "Milburn, Sarah", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(12)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BELGIAN, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Belgian, David"
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(13)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BELGIAN, Philip"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Belgian, Philip"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - BELGIAN, David").Select
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(14)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - BOYLE, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Boyle, Lynne"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(15)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - HIFTLE, Les"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Hiftle, Les"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(16)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - MCGILL, Ken"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "McGill, Ken"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(17)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - MILBURN, Sarah"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "McGill, Ken"
    Sheets("CH - SME").Select
    Sheets("CH - SME").Copy After:=Sheets(18)
    Sheets("CH - SME (2)").Select
    Sheets("CH - SME (2)").Name = "CH - YEAMAN, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=7, Criteria1:= _
        "Yeaman, Lynne"
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("CH - MILBURN, Sarah").Select
    Columns("G:G").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(19)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - COR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Melrose, Alison"
    Sheets("PH - COR").Select
    Sheets("PH - COR").Copy After:=Sheets(20)
    Sheets("PH - COR (2)").Select
    Sheets("PH - COR (2)").Name = "PH - ELLIOTT, Susan"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - COR").Select
    Sheets("PH - COR").Copy After:=Sheets(21)
    Sheets("PH - COR (2)").Select
    Sheets("PH - COR (2)").Name = "PH - MELROSE, Alison"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(22)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - MTR"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Baber, David", "Bailey, Michael", "Davis, Colin", "Dodsworth, Peter", _
        "Pozniak, Samantha", "Brown, Peter", "Brown, Janet", "Fenwick, Ray", "Tait, Debbie", "Dodsworth, Peter"), Operator:=xlFilterValues
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(23)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - BABER, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "=Baber, David", Operator:=xlOr, Criteria2:="=Davis, Colin"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(24)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - BAILEY, Michael"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Bailey, Michael"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(25)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - DAVIS, Colin"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Davis, Colin"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(26)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - POZNIAK, Samantha"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Pozniak, Samantha"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - MTR").Select
    Sheets("PH - MTR").Copy After:=Sheets(27)
    Sheets("PH - MTR (2)").Select
    Sheets("PH - MTR (2)").Name = "PH - WINLOW, Emma"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Winlow, Emma"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Sheets("Master").Copy After:=Sheets(28)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - SME"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "Lamb, Andrew", _
        "McGill, Ken", "Milburn, Sarah", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Sheets("PH - SME").Select
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Belgian, David", "Belgian, Philip", "Boyle, Lynne", "Hiftle, Les", "Lamb, Andrew", _
        "McGill, Ken", "Milburn, Sarah", "Yeaman, Lynne", "Keddie, Colin", "Stevenson, Noel", "Smedley, Gavin", "Fawkes, Anne", "Martindale, Alan", "Gibbon, Les", "Lamb, Andrew"), Operator:=xlFilterValues
    Columns("H:H").Select
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(29)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BELGIAN, David"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Belgian, David"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(30)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BELGIAN, Philip"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Belgian, Philip"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(31)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - BOYLE, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Boyle, Lynne"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(32)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - HIFTLE, Les"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Hiftle, Les"
    Selection.EntireColumn.Hidden = True
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(33)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - MCGILL, Ken"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "McGill, Ken"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(34)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - MILBURN, Sarah"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Milburn, Sarah"
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    ActiveWindow.ScrollWorkbookTabs Sheets:=-1
    Sheets("PH - SME").Select
    Sheets("PH - SME").Copy After:=Sheets(35)
    Sheets("PH - SME (2)").Select
    Sheets("PH - SME (2)").Name = "PH - YEAMAN, Lynne"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:= _
        "Yeaman, Lynne"
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Sheets("Master").Copy Before:=Sheets(29)
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = "PH - PPPRET"
    ActiveSheet.Range("$A$1:$M$464").AutoFilter Field:=8, Criteria1:=Array( _
        "Hurst, Sue", "Potts, Lesley Ann", "Shaw, Tracy", "Talbot-Jones, Richard", _
        "Watson, Nichola", "Bull, Lucy", "Herdman, Richard", "Hodgson, Irene", "Taylor, Helen"), Operator:=xlFilterValues
    Columns("H:H").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.SmallScroll ToRight:=-2
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Master").Select
    Range("B1").Select
End Sub

Open in new window

All the .PageSetup commands are in the same With clause and the Application.PrintCommunication statements are removed. Looks like a bug Excel 2010.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now