Accessing newly created worksheet in Excel

[Punky]

Hi all,
I've created a workbook to play a game with my friends. Each time I insert
results a new worksheet should be created (which happens) and then this
worksheet should be activated. I'm fairly new to VBA but have used object
oriented programming before.
The following code I created with the help of the build-in help file.
' werkblad voor uitslag per race maken
ActiveWorkbook.Sheets.Add _
after:=Worksheets(Worksheets.Count)
iWerkbladen = Sheets.Count
Worksheets("Sheet" & iWerkbladen).Activate // this line is
listed as 'the problem'
ActiveSheet.Name = sRace
Range("A1").Text = "Deelnemer"
Range("B1").Text = "Punten"

The error message I get is: subscript out of range
Can any of you please tell me what I'm doing wrong and how to do it
correctly?
T.i.a.
--
Punky

Leven is leven dat leven wil te midden van leven dat leven wil (dr. A.
Schweitzer)

[Veign]

See inline below...

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--

"Punky" <li*********@wanadoo.nl> wrote in message
news:42***********************@news.wanadoo.nl...

Hi all,
I've created a workbook to play a game with my friends. Each time I insert
results a new worksheet should be created (which happens) and then this
worksheet should be activated. I'm fairly new to VBA but have used object
oriented programming before.
The following code I created with the help of the build-in help file.
' werkblad voor uitslag per race maken
ActiveWorkbook.Sheets.Add _
after:=Worksheets(Worksheets.Count)
iWerkbladen = Sheets.Count
ActiveWorkbook.Sheets(.Sheets(Worksheets.Count+ 1).Name).Select
ActiveSheet.Name = sRace
Range("A1").Text = "Deelnemer"
Range("B1").Text = "Punten"

The error message I get is: subscript out of range
Can any of you please tell me what I'm doing wrong and how to do it
correctly?
T.i.a.
--
Punky

Leven is leven dat leven wil te midden van leven dat leven wil (dr. A.
Schweitzer)

[Punky]

"Veign" <[e-mail]> schreef in bericht
news:UC****************@newsread2.news.atl.earthli nk.net...

See inline below...

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--

"Punky" <li*********@wanadoo.nl> wrote in message
news:42***********************@news.wanadoo.nl...

Hi all,
I've created a workbook to play a game with my friends. Each time I insert results a new worksheet should be created (which happens) and then this
worksheet should be activated. I'm fairly new to VBA but have used object oriented programming before.
The following code I created with the help of the build-in help file.
' werkblad voor uitslag per race maken
ActiveWorkbook.Sheets.Add _
after:=Worksheets(Worksheets.Count)
iWerkbladen = Sheets.Count
ActiveWorkbook.Sheets(.Sheets(Worksheets.Count+

1).Name).Select

ActiveSheet.Name = sRace
Range("A1").Text = "Deelnemer"
Range("B1").Text = "Punten"

The error message I get is: subscript out of range
Can any of you please tell me what I'm doing wrong and how to do it
correctly?
T.i.a.
--
Punky

Leven is leven dat leven wil te midden van leven dat leven wil (dr. A.
Schweitzer)

Found out that when you create a new worksheet, it automatically gets focus,
so you don't need to activate it. So that's that problem solved.

Now out of the blue things that worked fine last year start creating errors
in the new app, when all I changed was the name of the app, which doesn't
appear anywhere in the code.
In the above code the lines Range ("A1").Text = "Deelnemer" and the one
below won't do what I expect them to and the lines I use (and have
successfully used last year) for writing data to cells give an error 424:
object requiered (or similar, language is Dutch (object vereist)). This is
the code I use:
If Range("M3").Text = sRace Then
ActiveCell.Offset(0, 12).Value = iPunten
It has to step through a number of cells to find the match and then write
the value to the appropriate cell.
I hope someone can help me.
--
Punky

Leven is leven dat leven wil te midden van leven dat leven wil (dr. A.
Schweitzer)

[Steve Gerrard]

"Punky" <li*********@wanadoo.nl> wrote in message
news:42***********************@news.wanadoo.nl...

| In the above code the lines Range ("A1").Text = "Deelnemer" and the
one
| below won't do what I expect them to and the lines I use (and have
| successfully used last year) for writing data to cells give an error
424:
| object requiered (or similar, language is Dutch (object vereist)).
This is
| the code I use:
| If Range("M3").Text = sRace Then
| ActiveCell.Offset(0, 12).Value = iPunten

In my Excel 97 help file, the Text property is listed as Read Only for
the range object.
I think using the Value property instead should work:

Range("A1").Value = "Deelnemer"

I'm not sure about the second one. However ActiveCell may not refer to
Range("M3"), if that is what you want. Maybe what you need is

Range("M3").Offset(0,12).Value = iPunten