Barryj
02-24-2007, 08:51 AM
I have data in sheet 1 that I want to transfer to Time Sheet Week1,
this data on sheet 1 runs down for 80 rows and the same for the time sheet.
I have recorded a macro of the first line but need help in making this macro as compact as possible, I also don't know how to get it to select each new row in sheet1 and place the data in the next time sheet page.
Can this be done by a loop, I have included a sample file to help show what I am trying to do.
Thanks for any assistance.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/02/2007 by Barry
'
'
Sheets("Time Sheet Week1").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[51]"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[-3]"
Range("E2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[2]"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[2]"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-1]"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[6]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[6]"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[3]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[10]"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[10]"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[7]"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[14]"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[14]"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[11]"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[18]"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[18]"
Range("G6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[15]"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-3]C[22]"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-3]C[22]"
Range("G7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-3]C[19]"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[26]"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[26]"
Range("G8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[23]"
Range("G9").Select
End Sub
this data on sheet 1 runs down for 80 rows and the same for the time sheet.
I have recorded a macro of the first line but need help in making this macro as compact as possible, I also don't know how to get it to select each new row in sheet1 and place the data in the next time sheet page.
Can this be done by a loop, I have included a sample file to help show what I am trying to do.
Thanks for any assistance.
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/02/2007 by Barry
'
'
Sheets("Time Sheet Week1").Select
Range("H1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[51]"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[-3]"
Range("E2").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[2]"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[2]"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-1]"
Range("B3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[6]"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[6]"
Range("G3").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[3]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[10]"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[10]"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC[7]"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[14]"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[14]"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[11]"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[18]"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[18]"
Range("G6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[15]"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-3]C[22]"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-3]C[22]"
Range("G7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-3]C[19]"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[26]"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[26]"
Range("G8").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[23]"
Range("G9").Select
End Sub