Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying data as static as source data changes
I have a query that pulls information from our unix server. Each day we update the query in sheet1 and the information in specific cells, G2 -G5 is now current through yesterday's business day. What I would like to do is create a table and copy that information once it's updated into a sheet2 as static values and have that listed under the day for which it's pertinent. I can do this manually, but I would like to have it done automatically if possible. Is there a way to have a formula only work once based on the current date and once it works, save the cell data as static and not a link becuase the next time the query is run the data changes. Any help is greatly appreciated. Peter -- pfrost ------------------------------------------------------------------------ pfrost's Profile: http://www.excelforum.com/member.php...o&userid=23164 View this thread: http://www.excelforum.com/showthread...hreadid=521142 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying data as static as source data changes
On Fri, 10 Mar 2006 11:16:09 -0600, pfrost
wrote: I have a query that pulls information from our unix server. Each day we update the query in sheet1 and the information in specific cells, G2 -G5 is now current through yesterday's business day. What I would like to do is create a table and copy that information once it's updated into a sheet2 as static values and have that listed under the day for which it's pertinent. I can do this manually, but I would like to have it done automatically if possible. Is there a way to have a formula only work once based on the current date and once it works, save the cell data as static and not a link becuase the next time the query is run the data changes. Any help is greatly appreciated. Peter Sounds like you need a macro, which could be made to run automatically by the process which calsl your unix data. A simple Copy and Paste Special(Values) command in the macro should achieve what you want , assuming I've understood correctly. Rgds Richard Buttrey __ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying data as static as source data changes
That's What I assumed. I guess I'm looking for help with that sort of Macro. I don't have much, (really none) experience in writing macro's. I was hoping someone might have done something similiar previousely and could give me some pointers. Thanks for your response though! Rgds Peter -- pfrost ------------------------------------------------------------------------ pfrost's Profile: http://www.excelforum.com/member.php...o&userid=23164 View this thread: http://www.excelforum.com/showthread...hreadid=521142 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copying data as static as source data changes
On Fri, 10 Mar 2006 11:16:09 -0600, pfrost
wrote: I have a query that pulls information from our unix server. Each day we update the query in sheet1 and the information in specific cells, G2 -G5 is now current through yesterday's business day. What I would like to do is create a table and copy that information once it's updated into a sheet2 as static values and have that listed under the day for which it's pertinent. I can do this manually, but I would like to have it done automatically if possible. Is there a way to have a formula only work once based on the current date and once it works, save the cell data as static and not a link becuase the next time the query is run the data changes. Any help is greatly appreciated. Peter I've assumed that G2:G5 contains the table ofsummarised values you want to copy. If so the following is one solution Name the range G2:G5 on sheet 1 "Data". Presumably somewhere on Sheet 1 is a cell which contains the date for the day in question, so name that cell "Date" Now call the following procedure from the code procedure which loads your data. Sub CopyData() Dim dtMydate As Date dtMydate = Range("Date") Worksheets("Sheet2").Range("IV1").End(xlToLeft).Of fset(0, 1) = _ dtMydate Worksheets("Sheet1").Range("data").Copy Destination:= _ Worksheets("Sheet2").Range("IV2").End(xlToLeft).Of fset(0, 1) End Sub This will put the date on row 1 of Sheet2 and the G2:G5 data immediately underneath. Adjust the row references in "IV1" and "IV2" as necessary. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returned: Copying a formula horizontally, the source data is verti | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Reference multiple cells in if statement | Excel Worksheet Functions | |||
Formulas in source data | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |