PDA

View Full Version : [SOLVED] Copy paste help needed in macro



velocity1234
08-31-2014, 07:47 PM
I have the following code that copies a range and pastes it every 20 seconds on the next line in Sheet2. Here is the problem, when it fires off it jumps to Sheet2 performs the task and the pauses for 20 seconds and then loops again. So if I am on Sheet1 is routes me to Sheet2 and leave me there. If I move to sheet3 and then it fires off again it takes me back to sheet2.

What code do I need to have this macro work in the background while leaving me on sheet1 or whatever sheet I happen to be on? I've tried all sorts of variations but no luck. Thanks for taking a look.



Sub PasteGraphData()
' goes to sheet2 and locates "graphdata" C5:h5 then pastes it on every line
Application.Goto Reference:="GraphData"
Selection.Copy
'finds the next row to paste the data contained in Range "graphdata"
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' is a loop to copy and paste every 20 seconds
StartTimer ' Reschedule the procedure
End Sub

SamT
08-31-2014, 08:20 PM
Application.GoTos and Selects will do that to you.

Sub PasteGraphData()
Dim PasteRange As Range
Dim GraphData As Range

Set GraphData = Sheets("sheet2").Range("C5:H5")
Set PasteRange = Sheets("sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0)

GraphData.Copy
PasteRange.PasteSpecial (xlPasteValues)

Application.CutCopyMode = False

' is a loop to copy and paste every 20 seconds
StartTimer ' Reschedule the procedure

End Sub

velocity1234
08-31-2014, 08:30 PM
You are the MAN!!! thanks so much, it works perfectly, this was killing me.