software

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: www.excelforum.com/member.php...oamp;userid=23164
View this thread: www.excelforum.com/showthread...hreadid=521142On Fri, 10 Mar 2006 11:16:09 -0600, pfrost
gt; wrote:

gt;
gt;I have a query that pulls information from our unix server. Each day we
gt;update the query in sheet1 and the information in specific cells, G2
gt;-G5 is now current through yesterday's business day. What I would like
gt;to do is create a table and copy that information once it's updated
gt;into a sheet2 as static values and have that listed under the day for
gt;which it's pertinent. I can do this manually, but I would like to have
gt;it done automatically if possible. Is there a way to have a formula
gt;only work once based on the current date and once it works, save the
gt;cell data as static and not a link becuase the next time the query is
gt;run the data changes.
gt;
gt;Any help is greatly appreciated.
gt;
gt;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
__


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: www.excelforum.com/member.php...oamp;userid=23164
View this thread: www.excelforum.com/showthread...hreadid=521142On Fri, 10 Mar 2006 11:16:09 -0600, pfrost
gt; wrote:

gt;
gt;I have a query that pulls information from our unix server. Each day we
gt;update the query in sheet1 and the information in specific cells, G2
gt;-G5 is now current through yesterday's business day. What I would like
gt;to do is create a table and copy that information once it's updated
gt;into a sheet2 as static values and have that listed under the day for
gt;which it's pertinent. I can do this manually, but I would like to have
gt;it done automatically if possible. Is there a way to have a formula
gt;only work once based on the current date and once it works, save the
gt;cell data as static and not a link becuase the next time the query is
gt;run the data changes.
gt;
gt;Any help is greatly appreciated.
gt;
gt;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 quot;Dataquot;. Presumably somewhere on Sheet
1 is a cell which contains the date for the day in question, so name
that cell quot;Datequot;

Now call the following procedure from the code procedure which loads
your data.

Sub CopyData()
Dim dtMydate As Date
dtMydate = Range(quot;Datequot;)
Worksheets(quot;Sheet2quot;).Range(quot;IV1quot;).End(xlToLeft).Of fset(0, 1) = _
dtMydate
Worksheets(quot;Sheet1quot;).Range(quot;dataquot;).Copy Destination:= _
Worksheets(quot;Sheet2quot;).Range(quot;IV2quot;).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 quot;IV1quot; and quot;IV2quot;
as necessary.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________