Referencing a worksheet before it is created?!

Hi,

I have a worksheet (attached) that once filled in, a button is pressed and a new sheet is created which stores the data, and the original sheet is then cleared so it can be filled in again. I would like to automatically chart the data each week as it is filled in - but obviously I can't tell the chart plotter what worksheets to reference until they are actually created, as I don't know what they will be called (it all depends on what date the data is entered). Is there a way around this problem?

Thanks!

Apologies. Here it is. I'm not sure how I can get all the data to go into one sheet.

Hello,

Insofar as I understand your problem here is a track

1) copy the following code in a standard module

'### Adapt the constant ###
Const SOURCE As String = "Sheet1"
'##########################

Sub makeasheet()
Dim mydate As String
Dim S As Worksheet
Application.ScreenUpdating = False
If Sheets(SOURCE).Range("b8") = "" Then
  MsgBox "Date not entered in ''B8''"
  Exit Sub
End If
mydate = Application.WorksheetFunction.Text(Range("b8"), "dd-mm-yyyy")
On Error Resume Next
Set S = ActiveWorkbook.Sheets(mydate)
If Not S Is Nothing Then
  MsgBox "Date in use !" & vbCrLf & "either delete appropriate sheet or use another date", vbInformation
  Exit Sub
End If
Err.Clear
On Error GoTo 0
Sheets(SOURCE).Copy After:=Sheets(SOURCE)
Set S = ActiveSheet
S.Name = mydate '///
S.Shapes("Button 1").Delete
With Sheets(SOURCE)
  .Range("B2:E6,B8").ClearContents
  .Activate
End With
End Sub

2) create your chart in the worksheet "Sheet1", it will automatically be carried in the new sheet set.

Best regards.

PMO
Patrick Morange