Macro running on Windows but not on Mac

Question

I have written this code in Windows but it won't work in Mac. I know the code is a bit too long but I'm not sure which line maybe causing the problem. The error that I get on Mac is "Microsoft Excel isn't responding right now. Do you want to wait or quit?" Could you please help? Thanks!

Sub RefreshResume()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim i As Range

Sheets("Resume").Select
Range("Z:Z").ClearContents
Range("AZ2").Select
Selection.Copy
Range("A3:P5000").Select
ActiveSheet.Paste

Sheets("Pipeline").Select
Range("C5000").End(xlUp).Select
Range(Selection, Range("C2")).Select
Selection.Copy
Sheets("Resume").Select
Range("Z2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Selection.RemoveDuplicates Columns:=1, Header:=xlNo

Application.CutCopyMode = False

ActiveWorkbook.Worksheets("Resume").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Resume").Sort.SortFields.Add Key:=Range("Z3"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Resume").Sort
        .SetRange Range("Z:Z")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Range(Range("Z1"), Range("Z1").End(xlDown)).Select
Selection.Copy
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

Range("B3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[1]=Resume!RC[-1]),PipeLine!C[4],PipeLine!C[5])"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C=Resume!RC[-2]),PipeLine!C[5],PipeLine!C[6])"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-1]=Resume!RC[-3]),PipeLine!C[6],PipeLine!C[7])"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-2]=Resume!RC[-4]),PipeLine!C[7],PipeLine!C[8])"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-3]=Resume!RC[-5]),PipeLine!C[8],PipeLine!C[9])"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-4]=Resume!RC[-6]),PipeLine!C[9],PipeLine!C[10])"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-5]=Resume!RC[-7]),PipeLine!C[10],PipeLine!C[11])"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-6]=Resume!RC[-8]),PipeLine!C[11],PipeLine!C[12])"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-7]=Resume!RC[-9]),PipeLine!C[12],PipeLine!C[13])"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-8]=Resume!RC[-10]),PipeLine!C[13],PipeLine!C[14])"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-9]=Resume!RC[-11]),PipeLine!C[14],PipeLine!C[15])"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-10]=Resume!RC[-12]),PipeLine!C[15],PipeLine!C[16])"
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
    Range("O3").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(RC[-1]/SUMIF(PipeLine!C[-12],Resume!RC[-14],PipeLine!C[-11])-1,""n/a"")"


Range("B3:O3").Select
Selection.Copy
Range("A3").End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("A3").End(xlDown).Offset(1, 0).Select
Selection.Value = "Total"

Range("B3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("B3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("C3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("C3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("D3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("D3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("E3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("E3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("F3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("F3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("G3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("G3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("H3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("H3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("I3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("I3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("J3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("J3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("K3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("K3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("L3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("L3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("M3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("M3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("N3").Select
Set i = Range(Selection, Selection.End(xlDown))
Range("N3").End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)


Range("O3").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(PipeLine!C[-11])-1"

Range("B3:N3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "$#,##0"

Range("O3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0%"


Range("A3").End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


Range("A3").End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone


Range("A2:O2").Select
Selection.Copy
Range("A2").End(xlDown).Offset(2, 0).Select
ActiveSheet.Paste
Range("A2").End(xlDown).Offset(2, 0).Select
Selection.Value = "Manager"

Range("Z:Z").ClearContents

Sheets("Pipeline").Select
Range("B5000").End(xlUp).Select
Range(Selection, Range("B2")).Select
Selection.Copy
Sheets("Resume").Select
Range("Z2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Selection.RemoveDuplicates Columns:=1, Header:=xlNo

Application.CutCopyMode = False

ActiveWorkbook.Worksheets("Resume").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Resume").Sort.SortFields.Add Key:=Range("Z3"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Resume").Sort
        .SetRange Range("Z:Z")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Range(Range("Z1"), Range("Z1").End(xlDown)).Select
Selection.Copy
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False

Selection.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C=Resume!RC[-1]),PipeLine!C[4],PipeLine!C[5])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-1]=Resume!RC[-2]),PipeLine!C[5],PipeLine!C[6])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-2]=Resume!RC[-3]),PipeLine!C[6],PipeLine!C[7])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-3]=Resume!RC[-4]),PipeLine!C[7],PipeLine!C[8])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-4]=Resume!RC[-5]),PipeLine!C[8],PipeLine!C[9])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-5]=Resume!RC[-6]),PipeLine!C[9],PipeLine!C[10])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-6]=Resume!RC[-7]),PipeLine!C[10],PipeLine!C[11])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-7]=Resume!RC[-8]),PipeLine!C[11],PipeLine!C[12])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-8]=Resume!RC[-9]),PipeLine!C[12],PipeLine!C[13])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-9]=Resume!RC[-10]),PipeLine!C[13],PipeLine!C[14])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-10]=Resume!RC[-11]),PipeLine!C[14],PipeLine!C[15])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(PipeLine!C[-11]=Resume!RC[-12]),PipeLine!C[15],PipeLine!C[16])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
    Selection.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(RC[-1]/SUMIF(PipeLine!C[-13],Resume!RC[-14],PipeLine!C[-11])-1,""n/a"")"

Range("A2").End(xlDown).Offset(3, 1).Select
Range(Selection, Selection.Offset(0, 13)).Select
Selection.Copy
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


Range("A2").End(xlDown).Offset(3, 0).Select
Selection.End(xlDown).Offset(1, 0).Select
Selection.Value = "Total"


Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 1).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 1).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 2).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 2).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 3).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 3).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 4).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 4).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 5).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 5).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 6).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 6).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 7).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 7).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 8).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 8).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 9).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 9).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 10).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 10).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 11).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 11).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 12).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 12).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 13).Select
Set i = Range(Selection, Selection.End(xlDown))
Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 13).Select
Selection.End(xlDown).Offset(1, 0).Value = Application.WorksheetFunction.Sum(i)

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.End(xlDown).Select
Selection.Offset(0, 14).Select
ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(PipeLine!C[-11])-1"

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 1).Select
Range(Selection, Selection.Offset(0, 12)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "$#,##0"

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.Offset(0, 14).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0%"


Range("A2").End(xlDown).Offset(3, 0).Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Range("A2").End(xlDown).Offset(3, 0).Select
Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone

Range("Z:Z").ClearContents

Range("A1").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

Comment out `Application.DisplayAlerts = False` and `Application.ScreenUpdating = False` at the top and see if Excel is giving you some information. — Comintern, Feb 17 '17 at 16:16
Place a break point at the very start of your code and then debug it. You can keep stepping through it one line at a time until something breaks. Then let us know which line it is that is causing the problem. — Hasib_Ibradzic, Feb 17 '17 at 16:16
Also, just throwing this out there, the code may be running but the processing power that your code is taking could be the issue. With all of the times you are using selects, or filling down formulas you may be consuming more processing power than you have. Just a thought. — Brandon Barney, Feb 17 '17 at 16:31
I ***highly*** recommend reading through [how to avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros)...it will greatly help your code time, and abilities, and likely get rid of a few potential errors/unexpected things. — BruceWayne, Feb 17 '17 at 16:40

score 1 · Answer 1 · edited May 23 '17 at 12:09

I believe RemoveDuplicates does not work on a Mac e.g. Excel VBA - RemoveDuplicates method does not work with Mac. Step through the code line by line on the Mac by placing a breakpoint and see where the error occurs.

If you don't have a Mac to test on then get your code to write to a worksheet at certain points e.g:

Sheets("Log").Range("A1") = "About to try removing duplicates"
Selection.RemoveDuplicates Columns:=1, Header:=xlNo
Sheets("Log").Range("A2") = "Succeeded removing duplicates"

Then get the Mac user to email you the saved file. When you don't see the next log entry you've found the problem.

Macro running on Windows but not on Mac

1 Answers1