formula to remove formula???

[Mike]

Hi there,

I have a workbook that gathers daily work hours and OT hours for my
department from a central file that 20 other departments use. Everyday I open
it to get the previous days results but all the formulas from the last 2
weeks recalculate and take quite some time to finish. I know I can just copy
and past special on each row daily, but I want to streamline the workbook for
others to use with minimal training.

Is there a way to have the formula delete itself after it gets its results?
Or have the formula call a macro to copy/paste special in the active cell?
Can a formula call a macro?

Any help would be awesome

Mike

 

[Dave Peterson]

Nope.

Formulas can't do that kind of thing.

Maybe you could create a macro to copy|paste special values and then assign that
macro to a button on that worksheet. Just click the button when you want to run
the macro.

 

[Gord Dibben]

Formulas cannot call macros.

You would need some event code to change the formulas to values.

Perhaps a Workbook_Open event that does the trick when you open the workbook for
the first time each day.

Private Sub Workbook_Open()
With Sheets("MySheet")
.Range("A1:K1").Copy
.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP

 

[Mike]

Thats looks good Gord. Is there way to have it select only the row from the
previous day or the current day? Each of my rows start with the date. So each
day when I open it, it would look for the row with yesterdays or todays date
and run the macro. Then any formulas for the follwoing day would still be
there until I open it the next day.

Mike

 

[JLatham]

Mike,
I took Gord's basic code and added some stuff to it that will determine the
last row with a date earlier than the current day and take all of that
information and copy it and use the Paste Special | Values operation to
convert formulas to values. You can change the sheet name and the right-most
column needed to suit your actual setup. Hope this helps some.

Private Sub Workbook_Open()
Const sheetName = "MySheet" ' change as needed
Const LastColumnUsed = "K" ' change as needed
Dim copyAreaAddress As String
Dim LastInColA As Range
Dim rowOffset As Long

'start at the bottom of the list
'and look up for a date earlier than today
Set LastInColA = Sheets(sheetName).Range("A" & Rows.Count).End(xlUp)
Do Until LastInColA.Offset(rowOffset, 0) < (Now() - 1) And _
LastInColA.Offset((rowOffset - 1), 0).Row >= 1
rowOffset = rowOffset - 1
Loop
'check for "do nothing" situation
If LastInColA.Offset(rowOffset, 0).Row = 1 Then
Exit Sub
End If
'set up address for all data to be copied/pasted special
copyAreaAddress = "A1:" & LastColumnUsed & _
LastInColA.Offset(rowOffset, 0).Row

With Sheets(sheetName)
.Range(copyAreaAddress).Copy
.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

 

[Gord Dibben]

Thanks for jumping in Jerry

Hope it works for Mike

Gord

 

[JLatham]

Actually, as I look at it now, I'm not so certain that the Do loop shouldn't
be a While instead of an Until, because of the test to make sure we don't run
off of the top of the sheet. But it worked in testing with some dates on the
sheet that were earlier than current date...

As for jumping in, well it just seemed a small piece to add and besides, I
figured "why should Gord have ALL the fun?"

Happy New Year!