I have a column of formulas. The first cell in the column shows the
date the results were calculated. For example, cell A1 contains the
date 10/18/02 and below it in cells A2:A50 are results calculated from
data entered on 10/18/02. The data and the date of its entry are
entered elsewhere in the workbook.

Now for my problem: When new data and a new data entry date are
entered I would like for the date to appear automatically in the next
column, e.g., in cell B1 in the above example and for the results from
that data to appear in B2:B50--all of this without changing anything
in column A. Until a cell, e.g., B1 is used to display a date I want
it to show as a blank cell. This is because the formulas begin:
=IF(B1="","",.....)

The data and the data entry date are always entered in the same cells.
New data overwrites old data and the new date overwrites the old date.
I am only interested in keeping records of the formula results along
with the relevant date.

Milt

Hi
Please let me correct If my  understanding of problem was Wrong.
1. You have datas and Date entered somewhere in the sheet.
2. You calculate using formulas and as of now they are in A1 linked to
date and  A2:A50 contain formula results for that particular Date.
3. You want to keep the datewise Results in One col per day. starting
from A
   Next day result in B....

It can be easily done by the following macro.

Sub CopyResults()
    Sheets("Sheet1").Select 'Sheets where you stored your Result
formulas
    Range("A1:A50").Select  'Ranges where you stored your Result
formulas
    'Copy the Results
    Selection.Copy
    'Note the column value
    Colvalue = (Sheets("sheet1").Range("B51").Value)
    'Select Sheets where you want to Keep the results columnwise
    Sheets("Sheet2").Select
    Cells(1, Colvalue).Select
    'paste the result values
    Selection.PasteSpecial Paste:=xlValues
    Cells(1, Colvalue).Select
    'Format the top row for Date
    Selection.NumberFormat = "dd-mmm-yy"
    'Increment the colvalue by one and store it.
    'It will be used next time
    Sheets("sheet1").Range("B51").Value = Colvalue + 1
    MsgBox "Processing Over"
End Sub

How the program works:
It copies the result and paste on a new sheet starting from colA. The
Inital column value is stored in a Cell. After copying it was
increased by 1. So next day the results will be pasted in the next
column.


I assumed you written the formula for results A1:A50 in "sheet1". If
the sheet you has different name please change all the "Sheet1" in the
above macro to new one.
The macro first store initial column Value(1) in a Cell B51 in sheet1.
If you want to store somewhere else modify the code occordingly.

Firstday, the macro takes the initial col value (1), copy the results
from
Sheets A1:A50 and pastes them (only values not formulas) in "sheet2"
A1:A50.
If you want the results to be stored other than "sheet2" put that
sheet name in the above macro.

After copying the results, it increments the colvalue and stores. This
willbe used by the macro when it runs next day to identify the next
column.

Once work is over it will show the msg box "processing over". Click
ok.

How to run this program.
Copy this macro
Open your Excel file which contains results.
In the tools menu --Macro--Visual Basic Editor.
Paste the macro in the VB editor screen.
Do whatever modifcation is needed.
In sheets "Sheet1" Range"B50" put the comment "Colvalue" 
and in "B51" put 1 (initial colvalue) 

Save it.
To run it after all new data entered From menu
Tools-->Macro-->Macros-->Select
the macro name "Copyresults"
Select Run.

If you have any doubt on the procedure please let me know.

Wish you best of luck.
RAC

Hi, I am reposting the program as I noticed after posting some
commentedwords went to the next rows due to Google wordwrap. I removed
that extra words going to next line(formulas).

Thanks,
RAC


Sub CopyResults() 
    Sheets("Sheet1").Select 'Sheets where you stored your Result
    Range("A1:A50").Select  'Ranges where you stored your Result
    'Copy the Results     Selection.Copy     'Note the column value 
    Colvalue = (Sheets("sheet1").Range("B51").Value) 
    'Select Sheets where you want to Keep the results columnwise 
    Sheets("Sheet2").Select     Cells(1, Colvalue).Select 
    'paste the result values     Selection.PasteSpecial
Paste:=xlValues
    Cells(1, Colvalue).Select     'Format the top row for Date 
    Selection.NumberFormat = "dd-mmm-yy" 
    'Increment the colvalue by one and store it.     'It will be used
next time
    Sheets("sheet1").Range("B51").Value = Colvalue + 1 
    MsgBox "Processing Over" End Sub

Hi rac!

Thank you for the macro; however, since I'm pretty much a novice at
virtual basic programming I haven't been able to implement it. Here is
where I am:

1. I want the date shown in cell k5 to appear in cell O3509, and 
2. I want the data shown in B2:B48 to appear in O3510:O3550, all of
this without changing any date or data in column P.
3. Cell O3510 should be formatted for a date and Cells O3510:O3550
should be formatted to show dollar amounts.

Here is the macro I tried, but when I run it I get an error:

Sub CopyMSdata()
    Sheets("Master PF").Select
    Range("B8:B48").Select
    Selection.Copy
    Colvalue = (Sheets("Master PF").Range("O3508").Value) 'Colvalue
    Sheets("Master PF").Select
    Range("O3510:O3550").Select
    Cells(1, Colvalue).Select
    Selection.PasteSpecial Paste:=xlValues
    Cells(1, Colvalue).Select
    Selection.NumberFormat = "dd-mm-yy"
    Sheets("Master PF").Range("O3508").Value = Colvalue + 1
    MsgBox "Processing Over"
End Sub

    Thank you for any help you can give me, and I will be happy for
you to receive the $25 fee since read2live said he will withdraw his
answer. Maybe you can just answer the question by saying "see my
comment" and collect the fee. Let me know if I can help on this.

Milt

Hi Milt,
You want to store the results in the same sheet 
Copy date from K5 to O3509
Copy data from B2:B48 to O0310 to ...
To cater this I modified the code slightly.
Now you have to store 15 in O3508 (The initial column value of O is 15.
First col A is 1, B is 2... So O is 15)


The working prg is here
Sub CopyResults()
'Select and copy date
    Range("K5").Select
    Selection.Copy
    Colvalue = (Sheets("Master PF").Range("O3508").Value)
    Range("O3509").Select
    Cells(3509, Colvalue).Select
    Selection.PasteSpecial Paste:=xlValues
    'Format the top row for Date
    Selection.NumberFormat = "dd-mmm-yy"
 'Select and copy data
    Range("B2:B48").Select
    Selection.Copy
    Range("O3509").Select
    Cells(3510, Colvalue).Select
    Selection.PasteSpecial Paste:=xlValues
    Selection.NumberFormat = "[$$-409]#,##0.00"
  'Increment the Col value
    Sheets("Master PF").Range("O3508").Value = Colvalue + 1
    MsgBox "Processing Over"
End Sub

Please try this and let me know the results or any doubts.

Thanks,
RAC

Hi,
Please add line
Sheets("Master PF").Select 
before 
Range("K5").Select     

Missed out while typing.Sorry.

Thanks.
RAC

Hi rac,

THANKS! I tried the new macro and it works like a charm. This will
save me lots of time and anguish.

I think I understand the macro, except for one part. Under 'Select and
copy data the data is copied from B8:B48 and then it is pasted to:

Range("O3509").Select
Cells(3510 Colvalue).Select

Why is it that in neither of these two lines of code do you specify
the entire range (O3510:O3550) into which the data is to be pasted?

Milt

Hi Milt,
The explanation for the 2 lines
Range("O3509").Select 
Cells(3510 Colvalue).Select 

1. Range("O3509").Select  Actually it is a redundant line. Even if you
comment it the program will work with out any problem. I put it as it
helped me to understand that we are copying some where near O3509.

2.Cells(3510 Colvalue).Select 
This selects only one cell. Eventhough multiple cells are copied, for
pasting only one starting cell reference is enough. The cells will get
pasted from that starting cell.
You can also give range for pasting. But in that case the range of
cell copied and range of pasting should match. That is if you copied
10 cells you have to provide exact range of 10 cells Else you will get
error. So the easy way is give only one cell ref. You can verify this
manually also.

Hope this clarified your doubt.

Feel free to ask if you have any further details. I am happy to note
that, I am able to help to solve a problem.
By the way Now I am not a Paid Researcher of Google.
I want to become one If Google admits in future. So regarding  your
fees I leave it to you and Google to decide.

Thanks,
RAC

Hi RAC,

Thank you for the explanation of the coding. I need to learn some
Visual Basic for my project but I've been putting it off. Your
solution to my question and your explanations have helped a lot.

My question is again "open." I'll leave it that way in case you can
get approved as a researcher in time to answer it. As I said, your
answer can consist simply of "see my comments."

Thanks again for all your help.

Milt