Excel Crashes When Importing Data File

[JayWes]

I've writen code to open up .dat extension files in excel, copy the 1st
worksheet of the dat workbook, and paste this worksheet as the last worksheet
into an Excel workbook that is essentially a database. The code then makes a
copy of this worksheet which is used to perform calculations and store
results and pastes it as the last worksheet. I'm some 323 worksheets into
assembling this database, and suddenly when I attempt to import new data,
Excel crashes. I would appreciate it if someone could take a look at the
relevant part of my code and give me some idea of what the problem is.

Thanks in advance,
Jason

Set Wka = Application.Workbooks("MPD.xls")

' Check to see if data already exists and give option to replace
newname = Typ & Dir & " " & Coating & " " & Grade & " " & Gage

For Each Wks In Wka.Worksheets
If Wks.Name = newname Then
Dim Msga, Msgb, Style, Title, Response
Msgb = newname & " data already exists. Do you want to replace the
data?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "Replace Data?"

Response = MsgBox(Msgb, Style, Title)
If Response = vbYes Then
Application.DisplayAlerts = False
Wka.Sheets(newname).Visible = True
Wka.Sheets(newname + "R").Visible = True
Wka.Sheets(newname).Delete
Wka.Sheets(newname & "R").Delete
Application.DisplayAlerts = True
Else
GoTo Line1
End If
End If
Next Wks


Dim SourceBook As Workbook
Dim RetVal As Boolean

' Turn off screen updating.
Application.ScreenUpdating = False

' Show the Open dialog box.
RetVal = Application.Dialogs(xlDialogOpen).Show("*.dat")

' Set an object variable for the workbook containing the data file.
Set SourceBook = ActiveWorkbook

' Copy the 1st Workheet From Workbook "Sourcebook" and paste at _
the end of Workbook "Wka".
SourceBook.Worksheets(1).Copy after:=Wka.Worksheets(Worksheets.Count)

'Name the worksheet based on selections
Wka.Activate
Set Wks = ActiveSheet
Wks.Name = newname

' Close the book containing the text file.
SourceBook.Close SaveChanges:=False

'INSERT A COPY OF THE DATA SHEET TO BE USED FOR CALCULATIONS
Wks.Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = newname & "R"
Set ws = ActiveSheet
Wks.Visible = False

 

[Bill Renaud]

Put the following line of code at the top of your module, if you don't
already have it:

Option Explicit

Then declare all variables and fix compiler errors.

Step into the code when the error occurs and let us know what line the
error occurs on.
I see a line of code "GoTo Line1", but I don't see any "Line1:" anywhere
in the program, for starters. When the code ends up on this line, is it
OK for the code to goto the very 1st line in the entire module and
restart there? This may not be what you want. Normally, a labeled line
would be something like:

If cond _
then
'Do something
else
Goto ContinueMyProcessing
end if

ContinueMyProcessing:
'Continue other processing here.