Hmmm... a couple of those questions didn't get interpreted the way I had hoped.
1.) How are you determining what original data row to copy?
In order to automate everything you need to know where to start, what row?
What is it that determines this? How can this be logically used in a macro?
2.) Why are you doing all this?
It's great that you're a nice guy, but what I was trying to get at was why do you take things from one sheet to another and back again?
an example as to why I ask... the move back and forth could be replaced by this if you knew what the row is and if it could be manipulated in place.
Code:
With Sheets("Sheet1").Rows(7)
.Copy
.Insert shift:=xlDown
Application.CutCopyMode = False
'now do the manipulation
End With