PDA

View Full Version : Solved: VBA code to periodically copy data



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

Bob Phillips
08-13-2009, 02:41 PM
See if this helps



Private Sub Data_Collect()

Dim i As Variant

With Worksheets("Spread")

i = .Range("B5").Value
' Current Row

.Range("C3:AI3").Copy ' Copy everything in this range
.Range("C" & i).PasteSpecial Paste:=xlPasteValues
End With

i = i + 1
'Worksheets("Spread").Range("B5").Value = i + 1

End Sub

nitzbar
08-17-2009, 11:45 AM
Thanks. This works. But what do I do if I need to have multiple workbooks running on the same instance of Excel ?

mdmackillop
08-18-2009, 05:46 AM
But what do I do if I need to have multiple workbooks running on the same instance of Excel ?
You remember to ask this in the first post!

Try


Option Explicit
Dim WB As Workbook
Sub Main_Data_Collect()
'Dim startRow As Integer
'Dim totalNumofRun As Integer
Dim endRow As Integer
Dim currentRow As Integer
Set WB = Workbooks("Sample.xls")
'startRow = Range("B2").Value
'totalNumofRun = Range("B3").Value
endRow = WB.Sheets("Spread").Range("B4").Value
currentRow = WB.Worksheets("Spread").Range("B5").Value
Dim starttime As Variant
Data_Collect
If (endRow >= currentRow) Then
starttime = Now()
Application.OnTime starttime + TimeValue("00:00:15"), "Main_Data_Collect"
End If

End Sub

Private Sub Data_Collect()

Dim i As Variant
With WB.Worksheets("Spread")
i = .Range("B5").Value
' Current Row
.Range("C3:AI3").Copy ' Copy everything in this range
.Range("C" & i).PasteSpecial Paste:=xlPasteValues
End With
i = i + 1
'Worksheets("Spread").Range("B5").Value = i + 1
End Sub