Copying data as static as source data changes

On Fri, 10 Mar 2006 11:16:09 -0600, pfrost
<[email protected]> 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
__

Thanks for your response Richard!

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.



Rgds

Peter

On Fri, 10 Mar 2006 11:16:09 -0600, pfrost
<[email protected]> 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).Offset(0, 1) = _
dtMydate
Worksheets("Sheet1").Range("data").Copy Destination:= _
Worksheets("Sheet2").Range("IV2").End(xlToLeft).Offset(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
__________________________