Link to home
Start Free TrialLog in
Avatar of slatefamily
slatefamily

asked on

Want to write a macro in Access to format Excel Spreadsheet

I recorded a macro in excel to format spreadsheets, instead of opening each spreadsheet to run macro can I do this from access? (this is a snipit) but i'm getting an error Compile Error (Sub or Function not defined)

Sub FormatServiceLineReport_HCH()
'
' Macro2 Macro
'

'
    Workbooks.Open FileName:= _
        "P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls"
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub
Avatar of Michael Vasilevsky
Michael Vasilevsky
Flag of United States of America image

Try the below, and make sure you've referenced the Excel library! (Tools -> References ->Microsoft Excel XX.0 Object Library); XX depends on which version of Excel you have installed.
Function FormatExcel
    Dim xcelwb As Excel.Workbook
    Dim xcelapp As Excel.Application
    Dim Sheet As Excel.Worksheet
    Dim rng As Excel.Range
    
    Set xcelapp = New Excel.Application
    Set xcelwb = xcelapp.Workbooks.Open("C:\MyExcel.xls")
    Set Sheet = xcelapp.ActiveWorkbook.Sheets(1)
    
    '>>>>>>>>>>>>>>>>format the excel data>>>>>>>>>>>>>>>>>>>
    With Sheet
        Set rng = .Cells("A1:B10")
        With rng
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        end with
    end with    

    xcelwb.Save
    xcelapp.Visible = True
    Set xcelapp = Nothing
    Set xcelwb = Nothing
    Set Sheet = Nothing
    Set rng = Nothing
End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Norie
Norie

Here's the late-binding version.
Sub FormatServiceLineReport_HCH()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Const xlGeneral = 1
Const xlBottom = -4107
Const xlContext = -5002

      Set xlApp = CreateObject("Excel.Application")
       
      Set xlWB = xlApp.Workbooks.Open("P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls")

      Set xlWS = xlWB.Worksheets(1)

      With xlWS.UsedRange
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     End With

     xlWB.Close SaveChanges:=True

     Set xlWB = Nothing
 
     xlApp.Quit

     Set xlApp = Nothing

End Sub

Open in new window

Avatar of slatefamily

ASKER

Set xlWS = xlWB.Worksheets(1)

Is that my worksheet?  Would I format it like this:

Set xlWS = xlWB.Worksheets(Pivot)

(if pivot is the name of my sheet?)
I am asking bc I am formatting 3 sheets within one workbook
You can use the index of the worksheet:

Set xlWS = xlWB.Worksheets(1)

for the first worksheet in the workbook, or the name

Set xlWS = xlWB.Worksheets("Pivot")
I assumed there was only one worksheet in the workbook, perhaps an exported table/query.

Anyway, in the code I posted the 1 is the worksheet index and refers to the 1st worksheet.

If you wanted to use a worksheet called Pivot you would use:

Set xlWS = xlWB.Worksheets("Pivot")

You mention 3 worksheets though, do you want to apply the same formatting to those worksheets?

Are they the only worksheets in the workbook?
I put in my whole excel macro below (I just recorded a macro as I formatted each worksheet).  I am sure there is a better way for me to do this but i'm not sure.  I added in a header and footer, bolded the top lines, made sure all the columns were formatted, and I made it printer pretty.
Sub FormatServiceLineReport_HCH()
'
' Macro2 Macro
'

'
    Workbooks.Open Filename:= _
        "P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls"
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E2").Select
    Columns("E:E").ColumnWidth = 35.29
    Columns("A:A").ColumnWidth = 17.71
    Columns("I:I").ColumnWidth = 20.14
    Range("A1:I1").Select
    Range("I1").Activate
    Selection.Font.Bold = True
    Columns("C:C").ColumnWidth = 9.14
    Columns("C:C").ColumnWidth = 11.14
    Range("H7").Select
    Rows("2:2").EntireRow.AutoFit
    Cells.Select
    Cells.EntireRow.AutoFit
    ActiveWindow.View = xlPageBreakPreview
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    Range("E5").Select
    Columns("E:E").ColumnWidth = 26.29
    ActiveWindow.View = xlNormalView
    ActiveWindow.View = xlPageLayoutView
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Newborn Service Line Report" & Chr(10) & ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("A2").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("E3").Select
    Selection.Font.Bold = True
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&18Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("E11").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("E7").Select
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView
    Sheets("Pivot").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.Font.Bold = True
    ActiveWindow.View = xlPageLayoutView
    Selection.Font.Bold = True
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("C7").Select
    Sheets("Newborn_Service_Line_Report").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 18.14
    Range("E9").Select
    Columns("F:F").ColumnWidth = 7.29
    Columns("G:G").ColumnWidth = 11.14
    Columns("H:H").ColumnWidth = 10
    Columns("I:I").ColumnWidth = 8.29
    Rows("1:1").EntireRow.AutoFit
    Columns("I:I").ColumnWidth = 8.86
    Rows("1:1").Select
    Selection.Font.Bold = True
    Rows("1:2").Select
    Range("A2").Activate
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll Down:=-24
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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("C:C").ColumnWidth = 9.71
    Columns("C:C").ColumnWidth = 10.71
    Columns("D:D").ColumnWidth = 14
    Columns("B:B").ColumnWidth = 12
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("C7").Select
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SmallScroll Down:=-15
    Range("A1").Select
    ActiveWindow.FreezePanes = False
    ActiveWindow.View = xlPageLayoutView
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("D8").Select
    Selection.Font.Bold = True
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("E1").Select
    Sheets("Pivot").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Count of Service Name" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Sheets("Needs_Correction").Select
    ActiveWindow.View = xlPageLayoutView
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Needs Service Name Correction" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
    Range("H8").Select
    Sheets("Pivot").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Count by Service Name" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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
End Sub

Open in new window

That code just appears to be repeating the same (or very similar) formatting.

What formatting are you trying to apply to each worksheet?
Seems when I add in the rest of my code it is hanging up on Range (compile error sub or function not defined)  If this question is outside of what I was asking just let me know and I'll create a new question.  
I am added in header and footer, bolding the top line, put the layout to landscape, put the top row to repeat on top, made sure that it was page break was good for printing
It's not really outside the question, though it does take things a little further.

Also, a couple of things aren't quite clear.

Which worksheets do you want to apply the formatting to?

Is it basically the same formatting but with some changes, eg different headers?

Where should the page breaks go?

There's quite a bit of code to go through as well, but that's one of the bad things about using the macro recorder - it records everything you do.

You can probaby start tidying up the code a bit.

Here's a small example of how you can do that.
Option Explicit

Sub FormatServiceLineReport_HCH()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet


    Set xlApp = New Excel.Application

    Set xlWB = xlApp.Workbooks.Open("P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls")

    Set xlWS = xlWB.Worksheets("Pivot")

    With xlWS.UsedRange
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    With xlWS
        .Range("A1:I1").Font.Bold = True
        .Columns("A:A").ColumnWidth = 17.71
        .Columns("C:C").ColumnWidth = 9.14
        .Columns("E:E").ColumnWidth = 35.29
        .Columns("I:I").ColumnWidth = 20.14
        .Rows("2:2").EntireRow.AutoFit
        .Cells.EntireRow.AutoFit
    End With

Open in new window

That's only a very small part, but past that point it all gets a bit confusing.:)
This is the code I came up with, now I am getting an error that the macros are disabled in this project.
Sub FormatServiceLineReport_HCH()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Const xlGeneral = 1
Const xlBottom = -4107
Const xlContext = -5002

      Set xlApp = CreateObject("Excel.Application")
       
      Set xlWB = xlApp.Workbooks.Open("P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls")

      Set xlWS = xlWB.Worksheets(Pivot)

      With xlWS.UsedRange
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = xlLandscape
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False

    With xlWS
        .Range("A1:I1").Font.Bold = True
        .Columns("A:A").ColumnWidth = 17.71
        .Columns("C:C").ColumnWidth = 9.14
        .Columns("E:E").ColumnWidth = 35.29
        .Columns("I:I").ColumnWidth = 20.14
        .Rows("2:2").EntireRow.AutoFit
        .Cells.EntireRow.AutoFit
    End With

     
 
    With xlWB.PageSetup

        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Count by Service Name" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .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

xlWB.Close SaveChanges:=True

     Set xlWB = Nothing
 
     xlApp.Quit

     Set xlApp = Nothing

End Sub

Open in new window

MacroError.doc
ok I figured out the macro error.  Now I am getting hung on this part:

     Set xlWS = xlWB.Worksheets(Pivot)

Compile Error- variable not defined
ok I figured out the Pivot error (it wasn't in quotations).  Now I am getting hung on this part:

 With xlWB.PageSetup
Run time error - object doesn't support
Can I add in landscape orientation and my header?
Pivot shuld be in quotes.

Yes you can add orientation and the header.

Try recording a macro when you do only that, nothing else.

By the way, you really should try using early binding as the first example I posted does.

That will make it easier to write the code as you'll get Intellisense and all the constants, eg xlLandscape, will be defined.
I got an error when I tried the binding.  I looked at my references and I have objects 12.0 checked.
What error did you get?

Did you remove the declarations of the constants that I included in the late-binding version?
I think I got my question answered, and should move on to another question since this is multi-faceted.  Thank you so much for all the information you provided me.