nitzbar
08-13-2009, 02:37 PM
Hi.
I have a spreadsheet in which I'm getting real time data fed into a few cells. I would like to set up a macro that copies data from these cells at fixed intervals and copies it line by late further down in the spreadsheet. I have some code that does this, but gives an error whenever I navigate between workbooks or even worksheets. Any suggestions ?
Sub Main_Data_Collect()
'Dim startRow As Integer
'Dim totalNumofRun As Integer
Dim endRow As Integer
Dim currentRow As Integer
'startRow = Range("B2").Value
'totalNumofRun = Range("B3").Value
endRow = Worksheets("Spread").Range("B4").Value
currentRow = Worksheets("Spread").Range("B5").Value
Dim starttime As Variant
Data_Collect
If (endRow >= currentRow) Then
starttime = Now()
Application.OnTime starttime + TimeValue("00:01:00"), "Main_Data_Collect"
End If
End Sub
Private Sub Data_Collect()
Dim i As Variant
i = Worksheets("Spread").Range("B5").Value
' Current Row
Worksheets("Spread").Range("C3:AI3").Select ' Copy everything in this range
Selection.Copy
Worksheets("Spread").Range("C" & i).Select 'Paste the copied data in this row
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
i = i + 1
'Worksheets("Spread").Range("B5").Value = i + 1
End Sub
I have a spreadsheet in which I'm getting real time data fed into a few cells. I would like to set up a macro that copies data from these cells at fixed intervals and copies it line by late further down in the spreadsheet. I have some code that does this, but gives an error whenever I navigate between workbooks or even worksheets. Any suggestions ?
Sub Main_Data_Collect()
'Dim startRow As Integer
'Dim totalNumofRun As Integer
Dim endRow As Integer
Dim currentRow As Integer
'startRow = Range("B2").Value
'totalNumofRun = Range("B3").Value
endRow = Worksheets("Spread").Range("B4").Value
currentRow = Worksheets("Spread").Range("B5").Value
Dim starttime As Variant
Data_Collect
If (endRow >= currentRow) Then
starttime = Now()
Application.OnTime starttime + TimeValue("00:01:00"), "Main_Data_Collect"
End If
End Sub
Private Sub Data_Collect()
Dim i As Variant
i = Worksheets("Spread").Range("B5").Value
' Current Row
Worksheets("Spread").Range("C3:AI3").Select ' Copy everything in this range
Selection.Copy
Worksheets("Spread").Range("C" & i).Select 'Paste the copied data in this row
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
i = i + 1
'Worksheets("Spread").Range("B5").Value = i + 1
End Sub