Error Number: 1004

[Tablecloth98]

I've been trying to set up a macro that sorts data from one sheet (called 'data' in the macro) to various appropriately named tabs on a template sheet. The template has a helper tab that lists the appropriate tab names. I'm trying to loop along the columns to create a tab with each different name (tab names are on row 2 of the helper tab).

The problem I'm having is that when I try to run the script it comes up with Error Number 1004 on line 210. Line 210 is:

210 ThisWorkbook.Sheets(“Template (2)”).Name = ThisWorkbook.Sheets(“Tab Helper”).Cells((i), 2).Value

Does anybody know what's wrong with the line? (please note that for data security reasons i can't post the whole script but am available to answer questions ).

 

[DanteAmor]

Check:
- Let there be a sheet with this name: "Template (2)"
- Let there be a sheet with this name: "Tab Helper"
- At the time of the error, what is the value of variable i. Check that a valid sheet name exists in that cell.

 

[Tablecloth98]

Check:
- Let there be a sheet with this name: "Template (2)"
- Let there be a sheet with this name: "Tab Helper"
- At the time of the error, what is the value of variable i. Check that a valid sheet name exists in that cell.

I've checked through all of this and it all looks fine. The i value never gets above 1 but the value in that tab looks fine too. I'm not really sure what to make of it

 

[Tablecloth98]

I've managed to remove sensitive info from the macro:

Option Explicit



Sub Sort()

On Error GoTo Error_Handler



‘Declares all of the necessary variables to run the AutoSort program

Dim App As Object, Itm As Object

Dim EmailSubject As String

Dim SendTo As String

Dim EmailBody As String

Dim ccTo As String

Dim i As Long

Dim Data As Workbook



Set Data = ActiveWorkbook



‘If statement to check if the data sheet is in a recognised format (If #1)

10 If Range(“B4”) = “Type” Then



‘Preps Data Sheet

20 Range(“C4”) = “Number”

30 Range(“D:E”).EntireColumn.Delete

40 Range(“E:I”).EntireColumn.Delete

50 Range(“E4”) = “Total”

60 Range(“F:G”).EntireColumn.Delete

70 Range(“F4”) = “C/A”

80 Range(“G1”).EntireColumn.Delete

90 Range(“J1”).EntireColumn.Delete

100 Range(“K:O”).EntireColumn.Delete

110 Rows(4).AutoFilter

120 ActiveSheet.Range(“$B$4:$W$1048576”).AutoFilter Field:=2, Criteria1:=”=”

130 Range(“A5:W1048576”).EntireRow.Delete

140 Rows(4).AutoFilter

150 Rows(4).AutoFilter



160 ThisWorkbook.Sheets(“Tab Helper”).Visible = True



170 For i = 1 To 500



180 If Not IsEmpty(Cells((i), 2).Value) Then



200 ThisWorkbook.Sheets(“Template”).Copy After:=ThisWorkbook.Sheets(“Template”)

210 ThisWorkbook.Sheets(“Template (2)”).Name = ThisWorkbook.Sheets(“Tab Helper”).Cells((i), 2).Value



‘Filters Data sheet to only include the relevant payment types

220 Data.Range(“$B$4:$R$1048576”).AutoFilter Field:=2, Criteria1:=ThisWorkbook.Sheets(“Tab Helper”).Range(Cells((i), 5), Cells((i), 500))

‘Cuts and pastes the types onto the relevant tab

230 Data.Range(“$B$5:$J$1048576”).Cut

240 ThisWorkbook.Sheets(ThisWorkbook.Sheets(“Tab Helper”).Range(Cells((i), 2))).Range(“A2”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



250 Data.Rows(4).AutoFilter

260 Data.Rows(4).AutoFilter



‘Loops back and proceeds with next i

270 Else

280 End If

290 Next i



‘Deletes the now blank rows from the data sheet

300 Data.Range(“$B$4:$R$1048576”).AutoFilter Field:=3, Criteria1:=”=”

310 Data.Range(“$A$5:$R$1048576”).EntireRow.Delete

320 Data.Rows(4).AutoFilter

330 Data.Rows(4).AutoFilter

340 Data.Range(“B5:J1048576”).Cut

350 ThisWorkbook.Sheets(“Template”).Range(“A2”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False





‘(If #1) Exit & MsgBox to notify of unrecognised data

360 Else

370 MsgBox “Unrecognised data source- Please click on the data sheet before hitting Ctrl+M.”

380 Exit Sub

390 End If



‘

‘

‘

‘ERROR HANDLER-----------------------------------------------------

Error_Handler_Exit:

On Error Resume Next





‘Cleans up settings changes

Application.ScreenUpdating = True

Application.DisplayAlerts = True

Application.Calculation = xlCalculationAutomatic

Application.EnableEvents = True



ThisWorkbook.Sheets(“Tab Helper”).Visible = False



Exit Sub



‘Displays Error box if there’s an error present

Error_Handler:



MsgBox “The following error has occurred” & vbCrLf & vbCrLf & _

“Error Source: Module1/Sort” & vbCrLf & _

“Error Number: “ & Err.Number & vbCrLf & _

“Error Description: “ & Err.Description & _

Switch(Erl = 0, “”, Erl <> 0, vbCrLf & “Line No.: “ & Erl), vbCritical, _

“An Error has Occurred!”



‘Sends Error report to Admin if Error occurs

Set App = CreateObject(“Outlook.Application”)



SendTo = redacted.email@domain.com

EmailSubject = “Sort Error Alert. Error Number: “ & Err.Number

EmailBody = “Error Source: Module1/Sort” & vbCrLf & _

“Error Number: “ & Err.Number & vbCrLf & _

“Error Description: “ & Err.Description & _

Switch(Erl = 0, “”, Erl <> 0, vbCrLf & “Line No.: “ & Erl)



Set Itm = App.CreateItem(0)

With Itm

.Subject = EmailSubject

.To = SendTo

.Body = EmailBody

.Send



End With



GoTo Error_Handler_Exit



End Sub

 

[DanteAmor]

First:
Remove this line On Error GoTo Error_Handler from your macro. It is not recommended that you use it. Since different errors can occur and you will not know the reason.
As far as possible you should use code to avoid errors.

Second:
You did not put in the post the value of i at the time of error

I ask you again, at the time of the error, what value does the variable i have?
You can know the value of i when the error occurs, press debug, bring the mouse closer to the variable, a small window is displayed with the value of i, you come to that value and write it here.
Ex:

Then it checks the content of cell B and the value of i and comes again, go here and put the content of that cell so that I can see it.

Last,

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[Tablecloth98]

First:
Remove this line On Error GoTo Error_Handler from your macro. It is not recommended that you use it. Since different errors can occur and you will not know the reason.
As far as possible you should use code to avoid errors.

Second:
You did not put in the post the value of i at the time of error

I ask you again, at the time of the error, what value does the variable i have?
You can know the value of i when the error occurs, press debug, bring the mouse closer to the variable, a small window is displayed with the value of i, you come to that value and write it here.
Ex:
View attachment 116971


Then it checks the content of cell B and the value of i and comes again, go here and put the content of that cell so that I can see it.

Last,

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

The mistake I was making was that I assumed the cell value would be in the order column,row (A2 being Cells(1, 2)). I've swapped them round as per your answer and it's fixed the issue Thank you for your help!