Formulas becoming values

[Andrew01]

Hi,
I have a peculiar thing happening with my excel spreadsheet, any ideas would be greatly appreciated.

I have two worksheets. The first one has a table that is being populated by a macro.

The second worksheet simply copies the values from the table in the first sheet. eg. I am inputting into the formula line: =Input!A15.

When the macro changes the values in the first sheet I need these to be updated in the second, however the cells in the second sheet change to values and therefore do not update. The formula line no longer says: =Input!A15, but just a value.

I'm really confused. I could send through my macro code but im not sure if it will help? Thanks

[Bob Phillips]

The code would be a good starting point Andrew.

[Andrew01]

________________________________________
Sub Looping()

Dim aCell As Range
Dim Spot As Range
Dim Time As Range

Set aCell = Range("B3:E3")
Set Spot = Range("B14:E14")
Set Time = Range("A14:A14")

Dim Counter As Integer

For Counter = 1 To 36

Time.Activate
Call CurrentTime
ActiveCell.Offset(Counter, 0).Select
ActiveCell = CurrentTime


aCell.Copy
Spot.Activate
ActiveCell.Offset(Counter, 0).Select
ActiveCell.PasteSpecial (xlPasteValues)

Function CurrentTime()

CurrentTime = Now

End Function
________________________________________________
The range "aCell" is inputed manually.
This code populates cells A15 to E50 of the first worksheet.
I then want to send these values to the second worksheet but everytime the macro repopulates the range, the second worksheet loses formulas, leaving only values. Any ideas?

[Bob Phillips]

Although I cannot believe that is your actual code, there is a line in there saying

ActiveCell.PasteSpecial (xlPasteValues)

Guess what that does.