Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to remove formula???
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to remove formula???
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. Mike wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to remove formula???
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 On Fri, 28 Dec 2007 15:41:00 -0800, Mike wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to remove formula???
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 "Gord Dibben" wrote: 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 On Fri, 28 Dec 2007 15:41:00 -0800, Mike wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to remove formula???
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 "Mike" wrote: 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 "Gord Dibben" wrote: 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 On Fri, 28 Dec 2007 15:41:00 -0800, Mike wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to remove formula???
Thanks for jumping in Jerry
Hope it works for Mike Gord On Fri, 28 Dec 2007 19:49:00 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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 "Mike" wrote: 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 "Gord Dibben" wrote: 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 On Fri, 28 Dec 2007 15:41:00 -0800, Mike wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to remove formula???
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! "Gord Dibben" wrote: Thanks for jumping in Jerry Hope it works for Mike Gord On Fri, 28 Dec 2007 19:49:00 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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 "Mike" wrote: 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 "Gord Dibben" wrote: 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 On Fri, 28 Dec 2007 15:41:00 -0800, Mike wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove the Formula but leave the value alone? | New Users to Excel | |||
Remove formula indicator | Excel Discussion (Misc queries) | |||
Remove Formula Bar / Toolbars etc | Excel Discussion (Misc queries) | |||
remove " $ " from formula | Excel Worksheet Functions | |||
How can I remove a formula from a cell and still have the value? | Excel Worksheet Functions |