auto adding of worksheet

[Kevin J Prince]

Hi Group,

Is it possible to have a 'something or other' which does the
following.....

I need to create a new worksheet every week which I enter data into. The
worksheet contains week-number (sorted that one) and an invoice number.
(Stuck here)

How do I do that? So that just pressing a button will create a templated
* worksheet with the name INVOICE XX where XX is an incremented number
based on the previous actual invoice. Also that the XX number is shown
within the actual invoice.

* I have a complicated worksheet with various fields to be filled in
each week. So I need to make a template. (Stuck here as well)

In my mind I have something like a control worksheet which gives me some
button or other using some VBA????

Using Excel 2003, Either XP or Vista

Regards
(and hope that makes sense???)

Kevin
--
"I live in my own little world.
But it's OK. They know me here."
= = = =
Kevin J Prince
http://www.1and1.co.uk/?k_id=5257507

 

[Dave Peterson]

This uses names like:

INVOICE 01
INVOICE 02
INVOICE 03
INVOICE 04
....

And assumes that the template worksheet is in the same workbook and it's named
Template (and it can be copied):

Option Explicit
Sub testme()

Dim iCtr As Long
Dim TestWks As Worksheet
Dim NameToUse As String

iCtr = 0
Do
iCtr = iCtr + 1
NameToUse = "INVOICE " & Format(iCtr, "00")
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(NameToUse)
On Error GoTo 0

If TestWks Is Nothing Then
'found a worksheet name that doesn't exist
Exit Do
Else
'already exists, keep looking
End If
Loop

Worksheets("Template").Copy _
after:=Worksheets(Worksheets.Count)

ActiveSheet.Name = NameToUse

End Sub

 

[Kevin J Prince]

A BIG Thanks for that, one question though to add on it........

How do I get that same Invoice number into the actual worksheet into a
cell?

Regards Kevin

This uses names like:

INVOICE 01
INVOICE 02
INVOICE 03
INVOICE 04
...

And assumes that the template worksheet is in the same workbook and it's named
Template (and it can be copied):

Option Explicit
Sub testme()

Dim iCtr As Long
Dim TestWks As Worksheet
Dim NameToUse As String

iCtr = 0
Do
iCtr = iCtr + 1
NameToUse = "INVOICE " & Format(iCtr, "00")
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(NameToUse)
On Error GoTo 0

If TestWks Is Nothing Then
'found a worksheet name that doesn't exist
Exit Do
Else
'already exists, keep looking
End If
Loop

Worksheets("Template").Copy _
after:=Worksheets(Worksheets.Count)

ActiveSheet.Name = NameToUse

End Sub

--
"I live in my own little world.
But it's OK. They know me here."
= = = =
Kevin J Prince
http://www.1and1.co.uk/?k_id=5257507

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim iCtr As Long
Dim TestWks As Worksheet
Dim NameToUse As String

iCtr = 0
Do
iCtr = iCtr + 1
NameToUse = "INVOICE " & Format(iCtr, "00")
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(NameToUse)
On Error GoTo 0

If TestWks Is Nothing Then
'found a worksheet name that doesn't exist
Exit Do
Else
'already exists, keep looking
End If
Loop

Worksheets("Template").Copy _
after:=Worksheets(Worksheets.Count)

With ActiveSheet
.Name = NameToUse
.Range("A999").value = NameToUse
End With

End Sub

 

[Kevin J Prince]

THANKS Dave,

Yes a big thanks, very helpful and works a treat.

Made a couple of mods
1.
As I had already done the first 29 the hard way ;-}

iCtr = 30
Do

2.
That's where I wanted the value (figure only)

With ActiveSheet
.Name = NameToUse
.Range("E3").value = iCtr
End With

Excellent NG, I should keep my eyes on it far more than I do.

Best Regards
Kevin

Option Explicit
Sub testme()

Dim iCtr As Long
Dim TestWks As Worksheet
Dim NameToUse As String

iCtr = 0
Do
iCtr = iCtr + 1
NameToUse = "INVOICE " & Format(iCtr, "00")
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(NameToUse)
On Error GoTo 0

If TestWks Is Nothing Then
'found a worksheet name that doesn't exist
Exit Do
Else
'already exists, keep looking
End If
Loop

Worksheets("Template").Copy _
after:=Worksheets(Worksheets.Count)

With ActiveSheet
.Name = NameToUse
.Range("A999").value = NameToUse
End With

End Sub

This uses names like:

INVOICE 01
INVOICE 02
INVOICE 03
INVOICE 04
...

And assumes that the template worksheet is in the same workbook and
it's named
Template (and it can be copied):

Option Explicit
Sub testme()

Dim iCtr As Long
Dim TestWks As Worksheet
Dim NameToUse As String

iCtr = 0
Do
iCtr = iCtr + 1
NameToUse = "INVOICE " & Format(iCtr, "00")
Set TestWks = Nothing
On Error Resume Next
Set TestWks = Worksheets(NameToUse)
On Error GoTo 0

If TestWks Is Nothing Then
'found a worksheet name that doesn't exist
Exit Do
Else
'already exists, keep looking
End If
Loop

Worksheets("Template").Copy _
after:=Worksheets(Worksheets.Count)

ActiveSheet.Name = NameToUse

End Sub

--
"I live in my own little world.
But it's OK. They know me here."
= = = =
Kevin J Prince
http://www.1and1.co.uk/?k_id=5257507

 

[Charlie Gowen]

Dave, this worked great. Is there a way to generate additional sheets with the value of a cell, e.g., A1, incorporated in the Tab Name?

Thanks,
Charlie in Virginia Beach

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Dave Peterson]

Probably. But if you're using a single cell (A1), then wouldn't you only be
able to generate a single sheet?