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

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
__

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

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
__________________________