vba help for copy range every 15 minutes

[shahnanu]

i get in my work sheet prices updated from exchange in row 2 every minutes
for 30 cells i.e.from cell A2 to Z2

i want to copy value of this row copied every 15 minutes in same worksheet
from row 3 onwards( or new rows ) for study of rate changes every minutes

i use excel 2007

thank you for the help
plz answer if u have any vba code to paste

 

[hiker95]

shahnanu,

1. You mentioned 30 cells. A2 thru Z2 is 26 cells. What cells make up the additional 4 cells?

2. Does the above mentioned range contain formulae, or, do you have a macro running the updated every 15 minutes?

3. What is the name of the worksheet where the updates are done?

4. Can we see your macro code?


If posting VBA code, please use Code Tags - like this:

[code]

'Paste your code here.

[/code]

 

[shahnanu]

shahnanu,

1. You mentioned 30 cells. A2 thru Z2 is 26 cells. What cells make up the additional 4 cells?

2. Does the above mentioned range contain formulae, or, do you have a macro running the updated every 15 minutes?

3. What is the name of the worksheet where the updates are done?

4. Can we see your macro code?


If posting VBA code, please use Code Tags - like this:

[code]

'Paste your code here.

[/code]

yaa sorry its 26 cells

this range is having+formula from work sheet (sheet1)which updates from stock web site every minutes
name of this worksheet is (sheet2)
i do not have any code i just have macro which is for (sheet1) work sheet and that updates every minutes

if u want macro for sheet1 worksheet is as follows

ub KickStart()
  Application.OnTime Now + TimeValue("00:00:05"), "My_Procedure"
End Sub
Sub My_Procedure()
Dim e As Long
Dim Ws As Worksheet
Set Ws = Worksheets("ln2,col9")
For e = 1 To 10
  Ws.Range("B" & e).Copy 'Sheets("a2:r2").Select
  Ws.Range("B" & e).PasteSpecial Paste:=xlPasteValues
Next e
Call KickStart
End Sub

thanks for the help

 

[hiker95]

shahnanu,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.
This will keep thread clutter to a minimum and make the discussion easier to follow.


We have a communication problem. Lets take it one step at TIME.

i get in my work sheet prices updated from exchange in row 2 every minutes
for 30 cells i.e.from cell A2 to Z2

You have two worksheets:
1. Which worksheet, Sheet1 or Sheet2, is receiving the updates from the website in row 2, range A2:Z2?

 

[shahnanu]

sheet 2 sir

 

[hiker95]

shahnanu,

sheet 2 sir

OK.


Then Sheet1 will be receiving the data from Sheet2, range A2:Z2 values, beginning in row 1?

And, for each time interval, Sheet2 range A2:Z2 values will be copied to the next available blank row in Sheet1?

 

[shahnanu]

i think some communication problem is there now i made one excel sheet as follows to simplify

there r no macro used now till..sheet 1 recevies data with data communication from web

and one colum which has position data is pasted to sheet 2 by +sheet1!cell to sheet2 a2 to z 2

now i need in sheet 2 only copy value of a2:z2 every 15 minutes

to row 3 onwards

i think this clarifies the query

thanks for so much pain taken by u

 

[hiker95]

shahnanu,

Sample worksheet Sheet2 before the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3
4
5
6
7
8
Sheet2

i get in my work sheet prices updated from exchange in row 2 every minutes
i want to copy value of this row copied every 15 minutes

Sample worksheet Sheet1 before the macro (the numbers and formulae you see are there to simulate the prices updated from exchange):

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	1
2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2
Sheet1

After several 15 minute intervals we end up with something line this:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	7
2	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4
Sheet1

And this:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1
2	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2
3	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3
4	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4
5	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5
6	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6
7	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7
8
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub KickStart()
  Application.OnTime Now + TimeValue("00:15:00"), "My_Procedure"
End Sub

Sub My_Procedure()
Dim nr As Long

nr = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
If nr = 2 And Sheets("Sheet2").Range("A1") = "" Then nr = 1

Sheets("Sheet2").Range("A" & nr).Resize(, 26).Value = Sheets("Sheet1").Range("A2:Z2").Value

Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1

Call KickStart

End Sub

Before you use the macros with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the KickStart macro.

 

[hiker95]

shahnanu,

now i need in sheet 2 only copy value of a2:z2 every 15 minutes

to row 3 onwards

Sorry, I missed that.


After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2	1.1	1.2
4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4	2.2	2.4
5	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6	3.3	3.6
6	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8	4.4	4.8
7	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6	5.5	6
8	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2	6.6	7.2
9	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4	7.7	8.4
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub KickStart()
  Application.OnTime Now + TimeValue("00:15:00"), "My_Procedure"
End Sub

Sub My_Procedure()
' hiker95, 03/21/2013
' http://www.mrexcel.com/forum/excel-questions/692648-visual-basic-applications-help-copy-range-every-15-minutes.html
Dim nr As Long

nr = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
If nr < 3 Then nr = 3

Sheets("Sheet2").Range("A" & nr).Resize(, 26).Value = Sheets("Sheet1").Range("A2:Z2").Value

Sheets("Sheet1").Range("A1").Value = Sheets("Sheet1").Range("A1").Value + 1

Call KickStart

End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the KickStart macro.

 

[shahnanu]

Dear hiker95,

Thank you very much for the great help and the code works very nice
thank you so much..
i wish i can say thanks personally for the pain u have taken..
wish u good health..