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