Reinstate formulas in specific cells

[usmc-r70]

I’ve made great use of this form to solve my Excel problems, but this not
alludes me; Need your expertise!

I have cells that originally contained formulas, but the 'user' has the
option to input raw data into those cells. I need a way to reinstate those
formulas into a range of cells on a row-by-row basis.

The original cells with formulas are contained in range: R11:V74

In cell R4 I have a cell where the ‘user’ can select the ROW where they want
the formulas reinstated.

In cells R3:V3 I have the formulas ready to be copied and pasted into
appropriate cells on the selected ROW.

Additionally,
I need a solution that can be applied to other worksheets within the workbook.

If the ROW selected is outside the first row (row 11), or outside the last
row (74) it does nothing and returns the cursor to the row selection box in
cell R4. However, the user can add / delete rows so the solution needs to
determine where the last row of data is located. The starting row of data
will always be 11.

 

[Patrick Molloy]

on the sheet's code page (right click the tab and select View Code from the
popup menu)

Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
If target.Address = Range("R4").Address Then resetrow (target.Value)
End Sub
Sub resetrow(Rw As Long)
If Rw < 11 Or Rw > Range("R11").End(xlDown).Row Then Exit Sub
Range("R3:V3").Copy
Cells(Rw, "R").PasteSpecial xlAll
Application.CutCopyMode = False
End Sub

 

[usmc-r70]

This being my first 'thread' submission I am unfamiliar with the processes
and what buttons to push.

Patrick, I inadvertently pushed ‘NO’ on the ‘Did this post answer the
question?’ before I considered the options, please accept my apology.

After re-typing, re-reading and re-pasting the code I am still not able to
effect the ‘copy – paste’ functionality.

Is the intent for code to execute once the ‘User’ enters a row number in
cell R4 and selects ‘ENTER’?

When I ‘Right Clicked’ on the ‘Sheet Name’, and selected ‘View Code’ the
default ‘Sheet 3’ window opened up with ‘Module 1’ in the background. I
tried the code in both windows, with no action from the code that I could
observe.

After some additional though on my problem, I only need one cell, R3, from
the previous R3:V3 range to be copied and pasted into column R of the
selected row.

Any suggestions?