VBA - Insert new row and edit formula

K

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...


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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Welcome to MrExcel.

If you use FormulaR1C1 and relative references it won't matter where the activecell is.
 
Upvote 0
change to

Code: 
Range("D" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ5"
Range("E" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]Richard Pitman'!BJ6"
Range("F" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-67]C[56]"
Range("G" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-66]C[55]"
Range("H" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-65]C[54]"
Range("I" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-64]C[53]"
Range("J" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-63]C[52]"
Range("K" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-62]C[51]"
Range("L" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-61]C[50]"
Range("M" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-60]C[49]"
Range("N" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-59]C[48]"
Range("O" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-58]C[47]"
Range("P" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-57]C[46]"
Range("Q" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-56]C[45]"
Range("R" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-55]C[44]"
Range("S" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-54]C[43]"
Range("T" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-53]C[42]"
Range("U" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-52]C[41]"
Range("V" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-51]C[40]"
Range("W" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-50]C[39]"
Range("X" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-49]C[38]"
Range("Y" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-48]C[37]"
Range("Z" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-47]C[36]"
Range("AA" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-46]C[35]"
Range("AB" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-45]C[34]"
Range("AC" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-44]C[33]"
Range("AD" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-43]C[32]"
Range("AE" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-42]C[31]"
Range("AF" & ActiveCell.Row).FormulaR1C1 = "='[Total Hours 2012.xls]Richard Pitman'!R[-41]C[30]"
 
Upvote 0
Hi guys, sorry another question relating to this. Is there a way that I can change the name within the formula to whatever is entered in an input box.

For example in below, I would like to change the name 'Richard Pitman' to whatever is entered in an input box. Thanks in advance.

Code: 
Range("E" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]Richard Pitman'!$BJ$6"
 
Upvote 0
like this

Code: 
temp = InputBox("Enter your Data")
Range("E" & ActiveCell.Row).Formula = "='[Total Hours 2012.xls]" & temp & "'!$BJ$6"
 
Upvote 0
Thanks but I seem to be getting an error.

Error:
Run-time error '1004'
Application-defined or object defined error

In the debugger I can see that what I enter within the input box is being stored in 'temp' so I'm not sure what the problem is.

Thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Pasting string - variable in cell - ERROR
Replies
9
Views
260
Alex Blakenburg
A
C
  • Question
VBA: Filter specific data from table then make formula and put result to summary workbook
Replies
1
Views
373
HighAndWilder
H
M
  • Solved
Find and Replacing text in formula via VBA
Replies
5
Views
727
Joe4
Joe4
J
  • Question
VBA Code to Add Rows is slowing down
Replies
5
Views
492
JayB0730
J
H
  • Solved
Excel VBA file importing data from several files located in same folder -> challenge to copy data only (rest is working perfectly)
Replies
3
Views
100
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,214,405
Messages
6,119,320
Members
448,887
Latest member
AirOliver

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top