Formula-problem

[tsunami100]

Hi,

Everytime the user presses a button on a worksheet the code behind that button will add a new worksheet by number.

Private Sub cmbNewWeek_Click() 
  NumberOfSheets = Worksheets.Count 
  Sheets(1).Copy Before:=Sheets(NumberOfSheets) 
  Sheets(NumberOfSheets).Name = Str(NumberOfSheets) 
End Sub

This works, so this is ok BUT....
There is another sheet(DATASHEET) that should collect the data from one cell in every new sheet.
The moment the new sheet is added there is no data.
The moment the user adds data in that cell it has to be copied to the next cell in the DATASHEET.
Example :
Pressing the button adds sheet 2
Now in cell A2 from the DATASHEET has to come a Formula like this ='2'!I6

Pressing the button adds sheet 3
Now in cell A3 from the DATASHEET has to come a Formula like this ='3'!I6

Pressing the button adds sheet 4
Now in cell A4 from the DATASHEET has to come a Formula like this ='4'!I6

My problem is : How can i add in code those formulas

I need that DATASHEET to make a chart.

I hope you will understand what i'm trying to say here.
Thanks !!!


I choose my username Tsunami because i hope it keeps everybody remembering this terrible disaster.

 

[PHV]

Have you tried this ?
Sheets("DATASHEET").Cells(NumberOfSheets, 1).Formula = [tt]"='"[/tt] & NumberOfSheets & [tt]"'!I6"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tsunami100]

Hi PH,

Thanks for your quick reply and for a moment i tought this would be it but when i run the code, an Openfiledialog appears and in the upper left corner i see :
Update values: 4
The 4 comes from the sheetnumber
Any ideas ??
Thanks again !!!


I choose my username Tsunami because i hope it keeps everybody remembering this terrible disaster.

 

[tsunami100]

Never mind.
Problem solved !!!

I choose my username Tsunami because i hope it keeps everybody remembering this terrible disaster.