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

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 wrote:
>
> 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

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


In message <(E-Mail Removed)>, Dave Peterson
<(E-Mail Removed)> writes
>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 wrote:
>>
>> 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
>

--
"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

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

Dave Peterson wrote:
>
> 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 wrote:
> >
> > 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

--

Dave Peterson

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

In message <(E-Mail Removed)>, Dave Peterson
<(E-Mail Removed)> writes
>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
>
>Dave Peterson wrote:
>>
>> 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 wrote:
>> >
>> > 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
>

--
"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, 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

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

Charlie, Gowen wrote:
>
> 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