Why does this Crash

[Djblois]

This line of code keeps crashing only on one particular spreedsheet and it works on any other spreadsheet:

Set tempSheet = Worksheets.Add(, dataSheet)

I have declared tempsheet and datasheet to worksheets. Also, I have set up a watch for datasheet and it is correct. Why else might this line of code crash?? epescially since in crashes online on one particular workbook?

[Norie]

Are you trying to use dataSheet for the After argument?

If you are why not say so.


Set tempSheet = Worksheets.Add(After:=dataSheet)

[Djblois]

Norie,

Thank you but it is still crashing. I have no idea why. It works anywhere else

[mikerickson]

Is dataSheet.Parent active when the code is run?


tempSheet = dataSheet.Parent.Worksheets.Add(After:=dataSheet) will fix things if that is the problem.

[Djblois]

What do you mean is datasheet.Parent active? What is .Parent?

[Djblois]

Also,

Doing more testing I have tested to see if the worksheet was protected? and no it wasn't? Also, I tested to see if the reason was because it was read only and no when the same file was no longer read only it still crashed. Mikerickson, I tried your code and it still crashes. I am running out of ideas here. Anybody else have an idea of what to test?

[lucas]

Hi Daniel,
Suggestion...Strip it down and post it....something we can't see is causing the problem. You're looking at it and you can't see it so how do you think we should proceed.

[Djblois]

Well oddly enough this works:

Sheets.Add
Set workingSheet = ActiveSheet
workingSheet.Name = "Codes"

What makes that weird is it only crashed on that one spreadsheet on that line.

[johnske]

try Worksheets.Add.Move after:=datasheet

[johnske]

Well oddly enough this works:

Sheets.Add
Set workingSheet = ActiveSheet
workingSheet.Name = "Codes"

What makes that weird is it only crashed on that one spreadsheet on that line.that's because an added sheet always becomes the active sheet. you can shorten that to something like this without 'setting' it...

Worksheets.Add
With ActiveSheet
.Name = "Codes"
.Move after:=datasheet
'do whatever else you want here
End With

[mdmackillop]

...or even
Worksheets.Add(after:=datasheet).Name = "Codes"