How use =now function and how to retrieve data from another shee?

**dstock** · 06-13-2005, 12:01 AM

I've searched through various threads and need two quick questions answered please

1. I need users of the worksheet to be able to hit a button(marco) and add the contents of say A1 to A2, and have A1 be totally clear after that. I also need them to only be able to do this after the work day, say 5. I know this is done with the now function but haven't got it yet

**mangesh_yadav** · 06-13-2005, 12:52 AM

First part.

Range("A2") = Range("A1")
Range("A1").ClearContents
or
Worksheets("Sheet1").Range("A2") = Worksheets("Sheet1").Range("A1")
Worksheets("Sheet1").Range("A1").ClearContents

Second part, what do you mean by work day 5.

Mangesh

**dstock** · 06-13-2005, 08:10 AM

Thanks for the reply. I work for an energy trading company and am making a position tracker. At midnight, or 0:00 Eastern Time, I need the contents of one cell to be summed with the contents of another. Example

Yesterday Today after the "rollover" or summation fo the two cells they would
(200) A 100 look like this. So when the clock hits midnight, it will show this:
100 B 400

Yesterday Today A and B are just different contracts, those rows are entirely
(100) A 0 independent of one another. I just need to be able to hit a
500 B 0 or marco or "button" on the spreadsheet for the traders so when they come in in the morning they can rollover their positions and have the today column clear to track the trades they will do for the new day. I hope that makes sense. Thanks for the hep!

**mangesh_yadav** · 06-13-2005, 08:16 AM

I am sorry, I could not understand, but if you want the above macro to work only after 5 pm, then use:

Private Sub CommandButton1_Click()

If Time > TimeValue("17:00:00") And Worksheets("Sheet1").Range("A1") <> "" Then

Worksheets("Sheet1").Range("A2") = Worksheets("Sheet1").Range("A1")
Worksheets("Sheet1").Range("A1").ClearContents

End If

End Sub

Mangesh

**dstock** · 06-13-2005, 09:46 AM

thanks for the reply. I posted that code in vb, now how to I assign it to a macro. I've already got the button on the sheet. When I right click and select assign macro I select it but it comes up with the macro cannot be found

**dstock** · 06-13-2005, 11:13 AM

I attached the file so it will make more sense. Basically what I need is a macro that by clicking it will sum cell C12 and F12 placing the sum in F12, C13 and F13 placing the sum in F13, and so on. We just need yesterdays volume to be combined with todays in each row. Each row basically represents a different product. The time requirement isn't an issue. Instead I would like a message to pop up when the macro is hit to ask "are they sure they want to rollover" Then by click yes or no it will do so accordingly. Thanks for all the help!

**dstock** · 06-14-2005, 10:58 AM

Second question for you is a conditional based calculation I believe. An example is attached. I know its probably easy but again I haven't got it yet, Thanks!

**mangesh_yadav** · 06-15-2005, 12:23 AM

Hi Dstock,

many people here do not open attachments for the fear of viruses. So it would be better if you could explain your query in the post.

Mangesh

**dstock** · 06-15-2005, 07:41 AM

Agreed. Only problem is when I copy and paste the formatting gets messed up sometimes. I will post add an attachment for those whose don't want to open an attachment. I got the conditional worked out. Mangesh thanks for all your help. More questions later today. Its probably bedtime over there

**mangesh_yadav** · 06-15-2005, 07:49 AM

not yet. just 5:30 pm.

Mangesh

Agreed. Only problem is when I copy and paste the formatting gets messed up sometimes. I will post add an attachment for those whose don't want to open an attachment. I got the conditional worked out. Mangesh thanks for all your help. More questions later today. Its probably bedtime over there

**dstock** · 06-15-2005, 09:36 AM

My second question, which I don't believe is possible, is it possible to only hide/unhide and certain second of the block of cells. For example, if you have a 10X10 are you are working with, can I choose to hide/unide say the middle3X3. I don't believe this is possible because it HAS to assign rows and columns for everything. Thanks!
Edit/Delete Message

That rollover function worked. I will be working on it more today and probably post something tomorrow. Thanks.

**dstock** · 06-15-2005, 12:08 PM

So I have grouped cells, but was wondering if it is possible for them to pop up in stead of down. When I click + they always collapse down, is there a way to have them collapse up instead?