Hi

I have 90+ rows of data that I want to transfer into a table format on another worksheet. At present I have laboriously been copying and pasting from one to the other but am losing the will to live:

Id like to create a macro to do this for me. Is it a matter of recording the macro to replicate as I copy and paste or is there a better way....I have dabbled with a pivot table but cannot get it to do what I want.

Here is the code from a recorded macro that does one person/row of data but I dont know how to make this automatic for all rows of data (see below):

Sub staff_rpt()
'
' staff_rpt Macro
'

'
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-133]C[2]"
Range("B137").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-134]C[2]"
Range("D137").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-134]C[1]"
Range("F137").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-134]C"
Range("B141").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF(Formal_obs_2013_14_sorted!R3C25:R3C196,Subject_Reports_2013_14!R[-1]C)"
Range("B141").Select
Selection.AutoFill Destination:=Range("B141:E141"), Type:=xlFillDefault
Range("B141:E141").Select
ActiveWindow.SmallScroll Down:=6
Range("B146").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIFS(Formal_obs_2013_14_sorted!R1C13:R1C196,RC[-1],Formal_obs_2013_14_sorted!R3C13:R3C196,R145C)"
Range("B146").Select
Selection.AutoFill Destination:=Range("B146:E146"), Type:=xlFillDefault
Range("B146:E146").Select
Selection.AutoFill Destination:=Range("B146:E155"), Type:=xlFillDefault
Range("B146:E155").Select
ActiveWindow.SmallScroll Down:=18
Range("A161:F163").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-114]C[9]"
Range("A161:F163").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-158]C[9]"
Range("A164:F166").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-161]C[28]"
Range("A167:F169").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-164]C[47]"
Range("A171:F173").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-168]C[10]"
Range("A174:F176").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-171]C[29]"
Range("A177:F179").Select
ActiveCell.FormulaR1C1 = "=Formal_obs_2013_14_sorted!R[-174]C[48]"
Range("A177:F179").Select
End Sub




Any help would be appreciated
Thanks