Moving Data on opening Excel

[Guest]

Hi there- trying to create a spreadsheet to track drug prices for prescribing
use.

I am using 2 columns- one to be blank to enter the currents day's prices,
and another one to show the previous day's prices.

2 questions....

1) Is there any way to set something up so that when I start the program,
the data from the previous day's "current" column transfers itself to the
"previous" column to give me a clear colum to enter the new day's current
prices???

2) Is there a function or way of retaining the minimum value EVER reached
from these sets of values being entered

Basically i want to enter the prices every day, but achieve a column that
records the "best price" ever reached. I do not want to be building up the
price change data from each days prices, as the sheet will become massive
with some 150 drugs potentially changing price every day

Hope this makes sense

 

[Guest]

Hi

Just to answer a few of your questions, and provide a possible direction.

Using Programming Code (VBA) a macro is Created in the Worksheet_Open event
to transfer yesterdays data into the required column (Col A) and (Col B) is
cleared for todays input - very possible and not too hard!

To retain the minimum value that that product has ever reached would require
data (in some form).

The following is just a suggestion

in a workbook on sheet1 = Daily Task sheet
ColA = ProductName(or however you want to Identify the product)
ColB = YesterdaysPrice
ColC = CurrentPrice
ColE = LowestPriceToDate

On opening the workbook, ColC is Pasted to ColB
and the macro compares the yesterdays Price to the Lowest Price, If Lower
updates the Lowest Price , else leaves it as is.

Hope this answers your questions

 

[Guest]

Could anyone please help me with the code to do this at all?????

Many thanks

Justin

 

[Roger Govier]

Hi

The following code assumes that Sheet1 contains the data, and that Row 1
is a header row.
It will copy the data from column C (current data) to column B
(previous day's data.
Historical low values for each product are held in column E, and will be
updated if yesterday's values are lower, by using column F as a
temporary column to compare data.

Adjust sheet names and column letters to suit.

Sub Auto_open()
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Sheet1") '<=== Change
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Columns("C:C").Copy Columns("B:B")

Application.CutCopyMode = False
Range("F2").Activate
ActiveCell.FormulaR1C1 = "=MIN(RC[-1],RC[-4])"
Selection.AutoFill Destination:=Range("F2:F" & lr),
Type:=xlFillDefault
Range("F2:F" & lr).Copy
Range("E2").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("F2:F" & lr).ClearContents
Range("C2:C" & lr).ClearContents
Range("C2").Activate
End With
Application.ScreenUpdating = True
End Sub

You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.

To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

Because the macro is called Auto_open, it will run automatically when
you open the workbook.

If you want to create a shortcut, then Tools>Macros>Select the
macro>Options>enter a key to use with Ctrl>OK


If you are new to entering macros, then David McRitchie has lots of
useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm