kevintarr131
New Member
- Joined
- Jan 8, 2013
- Messages
- 6
Hi all,
I am trying to copy a row and then paste this on a row that the user has selected. Once this row has been inserted I then want to edit the formulas within each cell - This is where I am having trouble.
Because the row will be different each time (as the data is pasted into the currently selected row), my code below is useless as this only edits the formula within the cells of a set row.
Therefore I am trying to work out how once I have pasted the data, I can then edit the formula within the cells of the selected row.
I hope this makes sense but please let me know if you need this clearing up! My current code is below...
I am trying to copy a row and then paste this on a row that the user has selected. Once this row has been inserted I then want to edit the formulas within each cell - This is where I am having trouble.
Because the row will be different each time (as the data is pasted into the currently selected row), my code below is useless as this only edits the formula within the cells of a set row.
Therefore I am trying to work out how once I have pasted the data, I can then edit the formula within the cells of the selected row.
I hope this makes sense but please let me know if you need this clearing up! My current code is below...
Code:
'Unhide rows Rows("1:6").Select
Range("A6").Activate
Selection.EntireRow.Hidden = False
'Copy selection
Rows("1:2").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=63
'On selected row, paste
Rows(newRowNum).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("D74").Select
ActiveCell.Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ5"
Range("E74").Select
ActiveCell.Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ6"
Range("F74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-67]C[56]"
Range("G74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-66]C[55]"
Range("H74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-65]C[54]"
Range("I74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-64]C[53]"
Range("J74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-63]C[52]"
Range("K74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-62]C[51]"
Range("L74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-61]C[50]"
Range("M74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-60]C[49]"
Range("N74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-59]C[48]"
Range("O74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-58]C[47]"
Range("P74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-57]C[46]"
Range("Q74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-56]C[45]"
Range("R74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-55]C[44]"
Range("S74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-54]C[43]"
Range("T74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-53]C[42]"
Range("U74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-52]C[41]"
Range("V74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-51]C[40]"
Range("W74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-50]C[39]"
Range("X74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-49]C[38]"
Range("Y74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-48]C[37]"
Range("Z74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-47]C[36]"
Range("AA74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-46]C[35]"
Range("AB74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-45]C[34]"
Range("AC74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-44]C[33]"
Range("AD74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-43]C[32]"
Range("AE74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-42]C[31]"
Range("AF74").Select
ActiveCell.FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-41]C[30]"
'Hide rows
Rows("1:5").Select
Range("A5").Activate
Selection.EntireRow.Hidden = True