Automated sheet copying and naming
 

Hello all,

I am sure some of you will be able to help me.... but would you also like to
help?


I have a workbook for keeping track of competitions.
On the first worsheet I have an overview of results from the other
worksheets.
The second worksheet is a model for all other sheets.
In worksheet 1, cell a1 is the name (or date) of an event. Worksheet 3
should get the name of the event in that specific cell.
In worksheet 1, cell a2 is the name (or date) of enother event. Worksheet 4
should get the name of the event in cell a2.

In this way, I have to make a copy of the model worksheet and then give the
name in worksheet 1, cell a2 and next time of course, a3... and so on.
It would be terrific if Excel could make a copy of the model sheet and give
it the name of the event in ce a2, a3.. and so on, at the moment I give a
name in that cell.

I hope you understand my idea.

Also it would be very neat if it also works on Pocket excel (windows Pocket
PC 2003).


Thank you all,
Pierre

Automated sheet copying and naming
 

This requires VBA. Pocket excel does not support VBA to the best of my
knowledge.


Beyond that, use the Change event in the First worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sh As Worksheet
If Target.Count 1 Then Exit Sub
If Target.Column = 1 Then
If Not IsEmpty(Target) Then
If IsDate(Target) Then
sName = Format(Target, "yyyymmdd")
Else
sName = Target.Value
End If
On Error Resume Next
Set sh = Worksheets(sName)
On Error GoTo 0
If sh Is Nothing Then
Worksheets(2).Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sName
Else
MsgBox sName & " already exists"
End If
End If
Me.Activate
End If
End Sub



Right click on the tab of the overview sheet and select view code. Put in
code similar to the above.

--
Regards,
Tom Ogilvy


"Pierre De Cat" wrote in message
...
Hello all,

I am sure some of you will be able to help me.... but would you also like
to
help?


I have a workbook for keeping track of competitions.
On the first worsheet I have an overview of results from the other
worksheets.
The second worksheet is a model for all other sheets.
In worksheet 1, cell a1 is the name (or date) of an event. Worksheet 3
should get the name of the event in that specific cell.
In worksheet 1, cell a2 is the name (or date) of enother event. Worksheet
4
should get the name of the event in cell a2.

In this way, I have to make a copy of the model worksheet and then give
the
name in worksheet 1, cell a2 and next time of course, a3... and so on.
It would be terrific if Excel could make a copy of the model sheet and
give
it the name of the event in ce a2, a3.. and so on, at the moment I give a
name in that cell.

I hope you understand my idea.

Also it would be very neat if it also works on Pocket excel (windows
Pocket
PC 2003).


Thank you all,
Pierre