Macro Loop and Paste in next empty row

Hi All,
I was hoping you could help, as Im new to macros etc...

I recorded a macro, and I need it to loop AND to paste the next set of results in the next empty row...

Im guessing this is pretty basic stuff, as Ive seen countless threads etc giving examples, but I just have not managed to get any to work :0s

Could you help please?

The macro recorded is...

Sub reformat_data()
'
' reformat_data Macro
' Keyboard Shortcut: Ctrl+p
'
    Range("A2:G2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C2").Select
    ActiveSheet.Paste
    Range("C5:J37").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A35").Select
End Sub

When it loops, I need it to move down to the next A*:G* range until it hits an empty 'range'. The data copied from range C5:J37 then needs to be pasted in Sheet4, but in the next empty row.


Any ideas?


PS - sorry about not formatting the post correctly (earlier)

Rayhoop,

Welcome to the forum
Please take 5 mins to read the forum rules below and then amend your title and wrap your code

VBA Noob

Rayhoop,

Not sure I follow the logic of the code. Can you upload a sample workbook with a before and after or see if the below helps

Usually you can find the last cell with this statement

Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Where A = Last Value in Col A

This link explains in more detail

http://www.rondebruin.nl/copy1.htm

VBA Noob

Still not clear what your after. This copies Sheet 1 to Sheet 4. Note's may help

Sub CopyToNewSheet()
Dim SrcLastRow As Long
Dim DestLastRow As Long
Dim SrcSht As Worksheet
Dim DestSht As Worksheet

'Source data worksheet
Set SrcSht = Sheets("Sheet1")
'Destination worksheet
Set DestSht = Sheets("Sheet4")

'Source data Last Row using Column A
SrcLastRow = SrcSht.Cells(Rows.Count, "A").End(xlUp).Row

'Destination sheet Last Row using Column A
DestLastRow = DestSht.Cells(Rows.Count, "A").End(xlUp).Row + 1

'Copy Source sheet A2 to G and Last row to next row in Destination sheet
SrcSht.Range("A2:G" & SrcLastRow).Copy _
Destination:=DestSht.Range("A" & DestLastRow)

End Sub

VBA Noob