Run-time error ‘1004’: Method ‘Name’ of object ‘_Worksheet’ failed

[NewBlood]

I have inherited a file to perform a task. Whenever I run the "Process" button I get this error:
Run-time error ‘1004’: Method ‘Name’ of object ‘_Worksheet’ failed

Pressing the Process button should do the following:
1. Create new workbooks with a set filename
2. Filter data from the Data sheet
3. Copy filtered data in the created workbooks, separate sheets (renamed according to filters)

I have marked the code accordingly with: 'THIS IS THE LINE THE DEBUG POINTS OUT

Additional information, this code runs perfectly in a windows machine. I encounter this issue when using Mac.

I am very, very new to VBA, any help and guidance are appreciated.


Sub Process()

Run "Openfiles"

Dim x As Long, y As Long, teamtrgt As String, filetrgt As String, Celltrgt As String
Dim cellrange As Long, OMtrgt As String, ws As Worksheet

Windows("Macro file - extract and harvest v2.xlsm").Activate
Sheets("Macro Sheet").Select
x = 1
y = 0
cellrange = Range("a16").Value

Do Until x = Range("c1").Value

Range("D" & (x + 1)).Select
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

teamtrgt = Range("G2").Value
OMtrgt = Range("h2").Value
filetrgt = Range("i2").Value

On Error GoTo Sheetadd
Windows(filetrgt & ".xlsx").Activate
Sheets(teamtrgt).Select
GoTo SheetExisting

Sheetadd:
With ActiveWorkbook
Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
ws.Name = teamtrgt 'THIS IS THE LINE THE DEBUG POINTS OUT
End With

Windows("Macro file - extract and harvest v2.xlsm").Activate
Sheets("Data").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.Copy
Windows(filetrgt & ".xlsx").Activate
ActiveSheet.Paste
Resume Next

SheetExisting:
Windows("Macro file - extract and harvest v2.xlsm").Activate
Sheets("Macro Sheet").Select
Celltrgt = Range("j2").Value


Sheets("Data").Select
Cells.Select
Range("D1").Activate

ActiveSheet.Range("$A$1:$P$" & cellrange).AutoFilter Field:=14, Criteria1:=teamtrgt

Range("A2:P" & cellrange).Select
' Range(Selection, ActiveCell.SpecialCells(xlVisible)).Select
Selection.Copy

Windows(filetrgt & ".xlsx").Activate
Sheets(teamtrgt).Select
Range("A" & Celltrgt).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Windows("Macro file - extract and harvest v2.xlsm").Activate
Sheets("Data").Select
ActiveSheet.ShowAllData
Sheets("Macro Sheet").Select

x = x + 1

Loop

End Sub

[p45cal]

I can only think there may be something wrong with the name you're trying to give to the new sheet; it could have illegal characters in it (such as one of /:\?*[]) or that the sheet name already exists. In the Office 365 version the errors I get are:
'Run-time error 1004: That name is already taken. Try a different one'
and:
'Run-time error 1004: You typed an invalid name for the sheet or chart. Make sure that:…' etc.
but those messages may be different in other versions; which is your version?

Edit post posting:
If teamtrgt contains an empty string (Range("G2") is empty (going back further: Range("D" & (x + 1)) is empty)) I get the same message as you, so it's probably due to that.

Aother post posting edit:
Grrrr. I see that you've posted the same question at https://stackoverflow.com/questions/69214059/run-time-error-1004-method-name-of-object-worksheet-failed
and got a similar answer a few days ago; I've wasted my time.
Please have a read of http://www.excelguru.ca/content.php?184
Many forums such as these have a requirement to include links to cross posts.

[arnelgp]

use Option Explicit so you know which variables
you have not declared.

what does "OPenfiles" do?

what i see is that "somewhere" in your code, you have a variable teamtrgt
that holds the sheet name?
and you have an error handler that everytime an
error occurs, it will go and add new sheet and name it "teamtrgt".
but you failed to test if there already exists such a sheet?