-
Run-time error ‘1004’: Method ‘Name’ of object ‘_Worksheet’ failed
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
-
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/...rksheet-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.
Last edited by p45cal; 09-19-2021 at 01:22 PM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
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?
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules