Error 400

[24t42]

I am using Excel to help evaluate newsletters in a contest. The name of the club, name of newsletter and frequency of publication is entered on a page called AWARDS. I then have a CREATE TABS button, which creates the tabs and then transfers the appropriate information to the correct tab. As the newsletters are scored during the year, the sum of that issue is totaled and the yearly average figured. The award received is figured, and that information is transferred back to the AWARDS page. I designed it Excel 2010 but have saved it as Excel 97-2003.

It was working fine until today. It works for the first 4 tabs and then I get error 400. I am a rookie to Excel and have no idea what that means. I don't see a way to attach the file, so here is the VBA code I am using.

******************

Sub AddClubs()
'DEFINE VARIABLES HERE
Dim i As Integer, j As Integer
Dim ws1 As Worksheet, ws2 As Worksheet
Dim strX As String
Dim cel As Range
Dim Aws As Worksheet
Set Aws = Sheets("Awards")
' SET SETTINGS HERE
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set ws1 = Sheets("Awards")
Set ws2 = Sheets("Template")


For Each cel In Range("A2:A" & Aws.Range("A65536").End(xlUp).Row)
If Cells(cel.Row, 3).Text = "" Then
MsgBox "Sorry you are missing Frequency Information"
Cells(cel.Row, 3).Activate ''added this line of code
Exit Sub
End If
Next
'DELETE ANY UNWANTED SHEETS IF THEY EXIST.
For i = Worksheets.Count To 1 Step -1
If Worksheets(i).Name <> "Awards" And Worksheets(i).Name <> "Template" Then
Worksheets(i).Delete
End If
Next

'unprotec Awards sheet
ws1.Unprotect

'START ADDING THE TEMPLATES HERE AND RENAME THEM. THEN ADD THE CELL VALUES AS DESIRED.
For i = 2 To ws1.Cells(65536, "A").End(xlUp).Row
Sheets("Template").Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Left(ws1.Range("A" & i), 30)
ActiveSheet.Range("E1") = ws1.Range("B" & i)
ActiveSheet.Range("E2") = ws1.Range("A" & i)
ActiveSheet.Range("E3") = ws1.Range("C" & i) & " times a year"
'ws1.Range("D" & i) = "=Template!R[47]C[-2]"
strX = "='" & ActiveSheet.Name & "'!A51"
ws1.Range("D" & i) = strX
'Cells.EntireColumn.AutoFit
Next
ws1.Select
Range("A1").Select
'Cells.EntireColumn.AutoFit

' RESTORE ALL SETTINGS BACK HERE
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox ("Done!")

'Protect Awards Sheet
ws1.Protect

End Sub

********************
Any help would be appreciated.

 

[Joe4]

Welcome to the Board!

If you hit "Debug" when you get that error, which line of code does it highlight?

I would recommend going into the VB Editor and resize your code window to about one-quarter of the screen. Then use F8 to step through your code line-by-line. You should be able to see what it is doing on your Excel sheet as you are doing this. This should help you see what is going on, and exactly where it seems to be having issues.

One thing you may want to try is changing i and j to be declared as LONG instead of INTEGER. Integer only goes as high as 32,767. If you go past this row on your spreadsheet (i.e. 65,536), it could cause errors.

 

[24t42]

Thank you for very much for the suggestions. I am a rookie with Excel. I had help in writing the code and really don't understand why some things do what they do.

With that said, I found the problem quite by accident. It seems that when you are inputting data (the name of the club and newsletter) you cannot use an apostrophe. If you use an apostrophe, the program stops, and you get error 400.

I appreciate your help and will follow through with your suggestions.

Thanks again!!