Run-time error '1004'

[sunshine23111]

Good morning!

Someone wrote this wonderful piece of code for me. It runs on my Mac OS but when trying to run on a Windows PC, I get run-time error 1004: Unable to get the unique property of the Worksheet function class. Can someone help me to find out why it did not run on Windows. Is it something missing on the Windows laptop?

Public Sub Split_Sheet_By_Name()

Dim destFolder As String
Dim DistinctNames As Variant, DistinctName As Variant
Dim filteredCells As Range
Dim NameWorkbook As Workbook
Dim AutoFilterWasOn As Boolean

destFolder = Environ("USERPROFILE") & "\Desktop\"

Application.ScreenUpdating = False

With ActiveWorkbook.ActiveSheet

AutoFilterWasOn = .AutoFilterMode

DistinctNames = WorksheetFunction.Unique(.Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row))

For Each DistinctName In DistinctNames

'Filter on column A to show only rows for this Name

.Range("A1").CurrentRegion.AutoFilter Field:=1, Operator:=xlFilterValues, Criteria1:="=" & DistinctName
Set filteredCells = .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)

'Copy filtered cells to new workbook

Set NameWorkbook = Workbooks.Add(xlWBATWorksheet)
filteredCells.Copy NameWorkbook.Worksheets(1).Range("A1")
NameWorkbook.Worksheets(1).Range("A1").CurrentRegion.EntireColumn.AutoFit
Application.DisplayAlerts = False 'suppress warning if file already exists
NameWorkbook.SaveAs destFolder & DistinctName & ".xlsx", xlOpenXMLWorkbook
Application.DisplayAlerts = True
NameWorkbook.Close False

Next

'Restore autofilter if it was on

.AutoFilter.ShowAllData
If Not AutoFilterWasOn Then .AutoFilterMode = False

End With

Application.ScreenUpdating = True

MsgBox "Done"

End Sub

 

[Domenic]

Which version of Excel are you using? As per Microsoft's support page, the UNIQUE function is only available to the following versions of Excel...

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel for iPad Excel for iPhone Excel for Android tablets Excel for Android phones

Here's the link to that page...

UNIQUE function - Microsoft Support

Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.

support.microsoft.com

 

[sunshine23111]

Which version of Excel are you using? As per Microsoft's support page, the UNIQUE function is only available to the following versions of Excel...



Here's the link to that page...

UNIQUE function - Microsoft Support

Learn about the UNIQUE function, which returns a list of unique values in a list or range. UNIQUE is in a class of functions called dynamic arrays.

support.microsoft.com

Thanks. The laptop is running Student Version 2019. That sounds like my problem. Thanks so much!

 

[Domenic]

You're very welcome, cheers!