Macro to convert from Paste Bottom-Up to Top-Down?

[RINCONPAUL]

I have this code which copies & pastes row 5 into row 7 every 5 seconds, after it shifts all past data in row 7 down by one. I need to amend it such that it pastes from Row 5 to last vacant row after row 7. ie if data is populating rows 7 to 24, then on next refresh it will paste into row 25. At the moment it always pastes to row 7 and previous pastes shunt down by one.

I need to amend this code with your help.
Cheers


Sub Record_data()
'
' Record_data Macro
'
Sheets("Data").Select
Sheets("Data").Rows("5:5").Select
Sheets("Data").Range("B5").Activate
Selection.Copy
Sheets("Data").Rows("7:7").Select
Sheets("Data").Range("B7").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Data").Rows("7:7").Select
Sheets("Data").Range("B7").Activate
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

[SamT]

Try this
Edit "B" to a column that is always filled to the bottom of the data

Sub SamT
With Sheets("Data")
.Rows(5). Copy .Rows(.Cells(Rows.Count, "B").End(xlUp).Row + 1)
End With
End Sub

[RINCONPAUL]

SamT: Runtime error 1004 Application defined or object defined error

[RINCONPAUL]

SamT,
maybe I should provide a little more detail?
In the attachment, the code pastes row 5 to row 7. You can see from the time that the most recent paste 1:35:23 AM is at top. I want that at bottom where 1:35:38 AM is.

[SamT]

SamT: Runtime error 1004 Application defined or object defined error
.Rows(5). Copy .Rows(.Cells(Rows.Count, "7").End(xlUp).Row + 1)There ain't no such Column letter as "7"

Did you mean Column "G" which is Column #7?

OK, it looks like Column "C" is always used, ie, never empty
Sub SamT
With Sheets("Data")
.Rows(5). Copy .Rows(.Cells(Rows.Count, "C").End(xlUp).Row + 1)
End With
End Sub


Whenever this code is run, it will copy Row #5 to the bottom.
ie if data is populating rows 7 to 24, then on next refresh it will paste into row 25. Except when the "Bottom" is Row #5, then it will copy it to Row #6.
If that is a problem then use this longer version.


Sub SamT
Dim NR As Long 'NR stands for Next Row

With Sheets("Data")
NR = .Cells(Rows.Count, "C").End(xlUp).Row + 1
If NR = 6 then NR = 7 'Never paste to Row #6

.Rows(5).Copy .Rows(NR)
End With
End Sub

[RINCONPAUL]

No, that's completely stuffed things up! Forget it SamT, I'll find another solution
Thanks anyway

[SamT]

That's not even the same situation as in your first image. And you never once mentioned formulas, which require special treatment when C&Ping.

But you're welcome any way.

PS: thanks for telling me just how I managed to "stuff it up," when you think "7" is a Column Letter and that we can see your workbook from our desks.

PPS:And don't worry, I will remember this the next time you need help.

[RINCONPAUL]

SamT,
My choice of words as in "stuffed up", was merely descriptive of the code and not meant as any personal reflection. Sorry if I offended you.

Yes I misunderstood Rows.Count," " to be the count of rows! My mistake. If I knew what I was doing on wouldn't be asking the questions! I realised that seconds after posting and why I followed with a more descriptive post. Apologies
However:
What changed from rows 1 to 5 in first image to second, apart from Countdown timer reduced? Your code took over after that.
The first code I put up specifically had "Paste=xlValues"....what does that tell you...that formulas might be present?
Your code is copying column blocks not entire rows as per my initial code.

You can have results or excuses, you can't have both! But it's OK to blame me.
Cheers

[SamT]

If you had said "The Code needs to paste values," instead of rudely criticizing and rejecting my gift, one tiny edit would have fixed it.

Like you said, you can find another solution.