::

Hi there,

Looking for HELP on VB code for the following:

Need to copy and save specified ranges on a continual refreshing shee
to another specified range of a new sheet.

Say,

i) ‘r1’ is an imported sheet refreshing every 60 minutes ;
set by ---- Data Import External Data Data Rang
Properties Refresh every 60 minutes

ii) ‘Sr1’ is a recording sheet in match with sheet ‘r1’;

iii) Need to transfer/record cells M4:M17 and N4:N17 to cells J4:J1
and J20:J33 respectively.

iv) For every consecutive 180 minutes, records will be copied t
consecutive cells on sheet ‘Sr1’ such as

................................data in cells M4:M17 and N4:N17 o
sheet ‘r1’
60 minutes after : copied to cells K4:K17 and K20:K33
respectively on sheet ‘Sr1’;

2 x 60 minutes after : copied to cells L4:L17 and L20:L33
respectively on sheet ‘Sr1’;

3 x 60 minutes after : copied to cells M4:M17 and M20:M33
respectively on sheet ‘Sr1’;

and so on for 24 hours (i.e. 24 records)



Would appreciate help with appropriate VB code to do the job.

v) at cell Q4 of sheet‘r1’, I have a [ =NOW() ]
can I also have the exact time of every recording be placed at J3
K3, L3, M3, N3, ….) of sheet ‘ Sr1’ ?


vi) I have about 8 ~ 10 imported sheets set to refresh at almost th
same time.
Obviously there will be some hindrances if all of them refresh
record simultaneously.

Will there be any VB code telling them to DO one after th
completion of the other ?


Thanks in advance for any assistance.

EduardoD.


P.S. Have been to sites below but just could not find anythin
matching:

http://www.enhanceddatasystems.com/.../ExcelTimer.htm

http://www.mvps.org/dmcritchie/excel/datetime.htm

http://www.cpearson.com/excel/events.htm

http://www.rondebruin.nl/copy5.htm

http://www.contextures.com/tiptech.html


:

--
EduardoDo
-----------------------------------------------------------------------
EduardoDon's Profile: http://www.excelforum.com/member.php...nfo&userid=849
View this thread: http://www.excelforum.com/showthread.php?threadid=26857

Add an event handler to the sheet containing
the "self refreshing" querytables

Assuming your Querytable is named AdoQuery1,
and is located on sheet1 (or at least not on sheet2 :)

then following would record it's change in sheet2




Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Name.Name
Case Me.Name & "!AdoQuery1"
With Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Cells(1, 1) = Now
.Cells(1, 2) = Target.Cells(2, 1)
End With
Case Else
Stop
End Select
End Sub


Adapt as needed <g



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


EduardoDon wrote:


::

Hi there,

Looking for HELP on VB code for the following:

Need to copy and save specified ranges on a continual refreshing sheet
to another specified range of a new sheet.

Say,