Excel Syntax/Compile Error

Question

I'm using the below macros which have worked when inputting on a different computer, but on my current Excel I receive the syntax/compile error and can't figure out what is wrong in the red sections... Help! 

Sub Reports_Setup()

'

' Reports_Setup Macro

'

' Keyboard Shortcut: Ctrl+r

'

    ActiveCell.Range("A1:C1").Select

    With Selection

        .HorizontalAlignment = xlCenter

        .VerticalAlignment = xlBottom

        .WrapText = False

       .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = False

    End With

    Selection.Merge

    With Selection

        .HorizontalAlignment = xlLeft

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = True

    End With

    ActiveCell.Offset(1, 0).Rows("1:4").EntireRow.Select

    Selection.Delete Shift:=xlUp

    ActiveCell.Offset(1, 0).Rows("1:9").EntireRow.Select

    Selection.Delete Shift:=xlUp

    With ActiveCell.Characters(Start:=1, Length:=15).Font

        .Name = "Calibri"

        .FontStyle = "Bold"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    With ActiveCell.Characters(Start:=16, Length:=20).Font

        .Name = "Calibri"

        .FontStyle = "Bold"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    With ActiveCell.Characters(Start:=36, Length:=12).Font

        .Name = "Calibri"

        .FontStyle = "Bold"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

       .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    Cells.Replace What:="Sales Offices: ", Replacement:="", LookAt:=xlPart,

        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

        ReplaceFormat:=False

    With ActiveCell.Characters(Start:=1, Length:=4).Font

        .Name = "Calibri"

        .FontStyle = "Bold"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    With ActiveCell.Characters(Start:=5, Length:=42).Font

        .Name = "Calibri"

        .FontStyle = "Bold"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    With ActiveCell.Characters(Start:=47, Length:=14).Font

        .Name = "Calibri"

        .FontStyle = "Regular"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    With ActiveCell.Characters(Start:=61, Length:=12).Font

        .Name = "Calibri"

        .FontStyle = "Bold"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

    Cells.Replace What:="    Investment: Portfolio Manager: Full Name: ", _

        Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _

        False, SearchFormat:=False, ReplaceFormat:=False

  ActiveCell.Offset(10, 0).Range("A1:W1").Select

    Cells.Replace What:="Intrernational Sales", Replacement:= _

        "International Sales", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _

        False, SearchFormat:=False, ReplaceFormat:=False

End Sub

Answer 1

Hi

I stripped down a bit your macro

But to make it more effective we need a copy of your workbook and more details of your macro process and goals

*******************************************************************************

Sub Reports_Setup()

'' Reports_Setup Macro

'

''' Keyboard Shortcut: Ctrl+r

Application.DisplayAlerts = False

With Range("A1:C1")

    .HorizontalAlignment = xlLeft

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .AddIndent = False

    .IndentLevel = 0

    .ShrinkToFit = False

    .ReadingOrder = xlContext

    .MergeCells = True

End With

Range("A1:C1").Offset(1, 0).Rows("1:4").EntireRow.Delete Shift:=xlUp

Range("A1:C1").Offset(2, 0).Rows("1:9").EntireRow.Delete Shift:=xlUp

With ActiveCell.Characters(Start:=1, Length:=47).Font

    .Name = "Calibri"

    .FontStyle = "Bold"

    .Size = 10

    .Strikethrough = False

    .Superscript = False

    .Subscript = False

    .OutlineFont = False

    .Shadow = False

    .Underline = xlUnderlineStyleNone

    .ThemeColor = xlThemeColorLight1

    .TintAndShade = 0

    .ThemeFont = xlThemeFontMinor

End With

Cells.Replace What:="Sales Offices: ", Replacement:="", _

LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _

SearchFormat:=False, ReplaceFormat:=False

    With ActiveCell.Characters(Start:=1, Length:=72).Font

        .Name = "Calibri"

        .FontStyle = "Bold"

        .Size = 10

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ThemeColor = xlThemeColorLight1

        .TintAndShade = 0

        .ThemeFont = xlThemeFontMinor

    End With

Cells.Replace What:="    Investment: Portfolio Manager: Full Name: ", _

Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _

False, SearchFormat:=False, ReplaceFormat:=False

Range("A1:W1").Offset(10, 0).Replace What:="Intrernational Sales", Replacement:= _

"International Sales", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _

False, SearchFormat:=False, ReplaceFormat:=False

Application.DisplayAlerts = True

End Sub

**************************************************************************************

Do let me know if you need more help

Regards

Answer 2

On the first section in red you are missing the space and underscore at the end of the first line in red. (Add a space and underscore to end of first line in red)

Plus there should not be any blank lines between sections of a line of code that has the space and underscore line breaks in an otherwise single line of code. (Delete the blank lines between the lines in red.)