NewBlood
09-17-2021, 10:12 AM
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
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