Error 1004 - Method 'Name' of object '_Worksheet' failed

[karldou]

Hi, I have created a macro that will copy the contents of a list of data, then create multiple new sheets within the same workbook with variable names and then paste and perform a filter.

I have the majority of the macro complete and working, however, at the end of the program I receive the following error:

Run-Time Error 1004

Error 1004 - Method 'Name' of object '_Worksheet' failed

I'll explain the macro and then post the code, with the offending line in bold.

Firstly, the macro deletes any Worksheets other than the 4 defined in the If statement. This is to clear the previous data.

Then the macro looks at the Worksheet "Results", where there is a list of catagories that have appeared in a previous list of data sources (Column A). The macro then creates a new Worksheet using the first catagory name in Column A (starts at A2). The macro then changes to the "Source" worksheet, selects all the data on that sheet, copies it and then pastes it into the new worksheet created by the macro. It then performes a filter on that new worksheet in Column B and filters by the same variable used for the worksheet name.

Along with a few other bits of formatting, this macro repeats for every catagory listed in the "Results" sheet.
After the last catagory has been completed. I then get the error. So all the data I need is present, but I would not be able to use the worksheet due the the error appearing at the end.

[vba]Sub DataSort()
Dim i As Long
Dim ws As Worksheet, wsraw As Worksheet
'Dim VarCrit As Range
Application.ScreenUpdating = False

ThisWorkbook.Activate
Set wsraw = Worksheets("Results")

' delete all but Front, Source, Results Worksheets
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Front" Or ws.Name = "Source" Or ws.Name = "Results" Or ws.Name = "Data" Then
Else
ws.delete
End If
Next
Application.DisplayAlerts = True


For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
VarCrit = Worksheets("Results").Range("A" & i)
Sheets("Source").Select
AutoFilterMode = False
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wsraw.Activate
ws.Name = wsraw.Cells(i, "A") 'THIS IS THE LINE THE DEBUG POINTS OUT
With ws
ActiveSheet.AutoFilterMode = False
Application.CutCopyMode = False
Sheets("Source").Range("A:G").Copy
Sheets(Range("Results!A" & i).Value).Select
ActiveSheet.Paste
.Columns("A:G").ColumnWidth = 120
.Rows("1:50").EntireRow.AutoFit
.Columns("A:G").EntireColumn.AutoFit
ActiveSheet.AutoFilterMode = False
.Range("A1:G1").Select
Selection.AutoFilter
.Range("A1:G1").AutoFilter Field:=2, Criteria1:=VarCrit
Sheets("Source").Select
End With
Next
End Sub[/vba]

Any help with solving this annoying error would really be appreciated!
Thanks in advance, and sorry for the long post!
Karl

[shamsam1]

do attach work book ..its will be easy to test..

[Mavyak]

It looks like VarCrit is housing the same string you are trying to set the new worksheet's name to. Have you checked the value to make sure it is not too long and does not contain any illegal characters?

[karldou]

Thanks for the replies. I am not at work at the moment, so i cannot attach the workbook yet.

VarCrit is housing the same variable that is used for the Sheet name.
The variable is limited to 13 characters in length and does not contain any illegal characters.

Would it be better to remove the VarCrit from the code. It is only being referenced once, so i could easily just reference the actual range directly.

Thanks for all your help!

[karldou]

Morning, I have attached a copy of the workbook which includes amended data and the code that i posted here.

Thanks

[shamsam1]

try this...i have not tested this

[VBA]
Sub DataSort()
On Error GoTo ErrHandler:
Dim i As Long
Dim ws As Worksheet, wsraw As Worksheet
'Dim VarCrit As Range
Application.ScreenUpdating = False

ThisWorkbook.Activate
Set wsraw = Worksheets("Results")

' delete all but Front, Source, Results Worksheets
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Front" Or ws.Name = "Source" Or ws.Name = "Results" Or ws.Name = "Data" Then
Else
ws.delete
End If
Next
Application.DisplayAlerts = True


For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
VarCrit = Worksheets("Results").Range("A" & i)
Sheets("Source").Select
AutoFilterMode = False
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wsraw.Activate
ws.Name = wsraw.Cells(i, "A") 'THIS IS THE LINE THE DEBUG POINTS OUT
With ws
ActiveSheet.AutoFilterMode = False
Application.CutCopyMode = False
Sheets("Source").Range("A:G").Copy
Sheets(Range("Results!A" & i).Value).Select
ActiveSheet.Paste
.Columns("A:G").ColumnWidth = 120
.Rows("1:50").EntireRow.AutoFit
.Columns("A:G").EntireColumn.AutoFit
ActiveSheet.AutoFilterMode = False
.Range("A1:G1").Select
Selection.AutoFilter
.Range("A1:G1").AutoFilter Field:=2, Criteria1:=VarCrit
Sheets("Source").Select
End With
Next
ErrHandler:
Resume Next
End Sub
[/VBA]

[karldou]

Hi, thanks that does allow the program to complete... However it has now created extra worksheets that are blank.
The range being used for the worksheet names is Results!A2 downwards. The new worksheets at the end of the list have the names from Source!A2 downwards. Is there a way to amend the code to stop after it is finished with the Results!A column.

Thanks

[Bob Phillips]

[vba]

Sub DataSort()
Dim i As Long
Dim ws As Worksheet, wsraw As Worksheet
Dim wsSource As Worksheet
'Dim VarCrit As Range
Application.ScreenUpdating = False

ThisWorkbook.Activate
Set wsraw = Worksheets("Results")
Set wsSource = Worksheets("Source")

' delete all but Front, Source, Results Worksheets
Application.DisplayAlerts = False
For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Front" Or ws.Name = "Source" Or _
ws.Name = "Results" Or ws.Name = "Data" Then
Else
ws.delete
End If
Next
Application.DisplayAlerts = True

With wsraw

For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row

VarCrit = Worksheets("Results").Range("A" & i)
wsSource.AutoFilterMode = False
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wsraw.Activate
ws.Name = .Cells(i, "A") 'THIS IS THE LINE THE DEBUG POINTS OUT
ws.AutoFilterMode = False
Application.CutCopyMode = False
wsSource.Range("A:G").Copy ws.Range("A1")
ws.Columns("A:G").ColumnWidth = 120
ws.Rows("1:50").AutoFit
ws.Columns("A:G").AutoFit
ws.AutoFilterMode = False
ws.Range("A1:G1").AutoFilter
ws.Range("A1:G1").AutoFilter Field:=2, Criteria1:=VarCrit
Next
End With
End Sub
[/vba]

[karldou]

That works brilliant XLD! I understand what you did by defining the two sheets. If only I had of thought of that beforehand!

Many thanks guys for all your help!

[Bob Phillips]

I think that the key was that the code was getting confused as to what was the activesheet, so by defing sheet objects for all sheets we get away from using activesheet, and directly address the applicable sheet.