Copy all cells in range if not isempty

hi guys,

have a stock price/volume spreadsheet that updates in realtime.

goal is to run a macro that loops every second between 09:00:00 and 09:30:00 and copies all the cells from bVolume/aVolume (columns I/K) to corresponding columns O/Q, but only if values are nonzero

therefore, if the bVolume/aVolume is zero for any given price, it will not overwrite the previous copied volume

have attempted to write macro in attached s/s but can't quite perfect it

please help!

Dim timeToRun
Sub copyPrices()
timeToRun = Now + TimeValue("00:00:01")
Application.OnTime timeToRun, "rangePaste"
End Sub

Sub rangePaste()
Dim ocell, qcell As Range
For Each ocell In Range("I4:I65")
If IsNumeric(ocell) Then
ocell.Copy (Range("O4:O65"))

Next ocell

For Each qcell In Range("K4:K65")
If IsNumeric(qcell) Then
qcell.Copy (Range("Q4:Q65"))
End If
Next qcell

If Now < "9:29:58" Then
Call copyPrices
Else: Exit Sub
End If
End Sub


matchtest2.xlsm

I'm not 100% certain what you want but I think it is this:

Please Login or Register  to view this content.

i probably wasn't entirely clear what i wanted but have amended your code to achieve desired result

thanks for the help!

Please Login or Register  to view this content.

still having a minor issue here - this is the amended code however the cells with data (ie not empty) that should be copying to columns O and Q are not appearing - ie O/Q seem to be blanking themselves with each iteration

Please Login or Register  to view this content.

could somebody pls point me in the right direction? cheers

Works fine on your sample for me.