Runtime error with sheets.add

Hi NG,

probably this question has been asked before, but I couldn't find an answer.
Problem:
I have a Excel 2003 - VBA-module. In this module I need to create a new sheet.
the code looks like this:

Dim NameOfWorkbook as string
NameOfWorkbook = ThisWorkbook.Name

Workbooks(NameOfWorkbook).Sheets.Add
after:=Workbooks(NameOfWorkbook).Sheets(Sheets.Count)

if I try to add a sheet manually, everything works fine.

But I get a Runtime error "-2147319784 (80028018)
Method Add of object sheets failed"
when I try to add thew sheet using the code.

Any help would be greatly appreciated.

Regards
Bodo

Worksheets.Add after:=Worksheets(Worksheets.Count)

"ittac" wrote:

> Hi NG,
>
> probably this question has been asked before, but I couldn't find an answer.
> Problem:
> I have a Excel 2003 - VBA-module. In this module I need to create a new sheet.
> the code looks like this:
>
> Dim NameOfWorkbook as string
> NameOfWorkbook = ThisWorkbook.Name
>
> Workbooks(NameOfWorkbook).Sheets.Add
> after:=Workbooks(NameOfWorkbook).Sheets(Sheets.Count)
>
> if I try to add a sheet manually, everything works fine.
>
> But I get a Runtime error "-2147319784 (80028018)
> Method Add of object sheets failed"
> when I try to add thew sheet using the code.
>
> Any help would be greatly appreciated.
>
> Regards
> Bodo

Unqualified references?

Dim NameOfWorkbook as string
NameOfWorkbook = ThisWorkbook.Name

With ThisWorkbook
.WorkSheets.Add after:=.WorkSheets(.WorkSheets.Count)
End With

--
HTH

Bob Phillips

"ittac" <[email protected].(I_Hate_Spam)> wrote in message
news:[email protected]...
> Hi NG,
>
> probably this question has been asked before, but I couldn't find an
answer.
> Problem:
> I have a Excel 2003 - VBA-module. In this module I need to create a new
sheet.
> the code looks like this:
>
> Dim NameOfWorkbook as string
> NameOfWorkbook = ThisWorkbook.Name
>
> Workbooks(NameOfWorkbook).Sheets.Add
> after:=Workbooks(NameOfWorkbook).Sheets(Sheets.Count)
>
> if I try to add a sheet manually, everything works fine.
>
> But I get a Runtime error "-2147319784 (80028018)
> Method Add of object sheets failed"
> when I try to add thew sheet using the code.
>
> Any help would be greatly appreciated.
>
> Regards
> Bodo