The following macro copies data from a worksheet named "Data" starting at cell A2, and down to the last row with data, and copies five columns of data per row.
The Data is copied to alternate rows on a worksheet named "Report" starting at Row 5 (cell A5)
To use this macro, create a button and attach the code to it:
On the "Data" Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Report' or something else suitable.
Right-click the button again and select View Code
In the code window that opens enter this:
Private Sub CommandButton1_Click()
Dim rngDest As Range
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngCell As Range
Dim intOffset As Integer
On Error GoTo ErrHnd
'set start of source range
Set rngSrcStart = Worksheets("Data").Range("A2")
'set end of source range
Set rngSrcEnd = Worksheets("Data"). _
Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'set destination range start
Set rngDest = Worksheets("Report").Range("A5")
'set destination offset
intOffset = 0
'get each row of source data
For Each rngCell In Range(rngSrcStart, rngSrcEnd)
'copy five columns to destination row
rngCell.Resize(1, 5).Copy Destination:=rngDest.Offset(intOffset, 0)
'jump a row
intOffset = intOffset + 2
Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.
Click Save from the Visual Basic Menu.
Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).
Test this macro on sample data. The changes cannot be undone with the Undo button. Always make a backup of your data before running this macro.
Hope this is what you were looking for.