Pinpoint a moving cell

[chriswrcg]

I have a range of data that has a header name. on the left of this range is a hidden column that is combining two cells into one cell name.( name and date). An example of this would be the name in cell b1 and the date in b3.($B$1&"-"&$B$3)
I wish to copy this range of data down multiple times and with each copy change the name used as the header. The dates however will never change.
what I would like is a macro of formula that will automatically recognize the new name cell and change the left formula to display the date and the new name.

I have provided a working model to work with.

[Mumps1]

Please read Forum Rule #8 about cross-posting.
Cross-posted: https://www.mrexcel.com/forum/excel-...ving-cell.html

[Kenneth Hobson]

Select the range to copy and Run.

Sub ppCopy()
  Dim p1 As Range, p2 As Range
  Dim r As Long, c1 As Range, c2 As Range
  Dim c As Range, s As String
  
  Set p1 = Selection
  Set p2 = Application.InputBox("Select TopLeft cell to pasted selection to.", _
    "Select Cell", Type:=8)
  Set p2 = p2.Resize(p1.Rows.Count, p1.Columns.Count)
  p1.Copy p2
  
  Set c1 = p1(1, 2) 'The old Name cell.
  Set c2 = p2(1, 2) 'The new Name cell.
  For r = 3 To p2.Rows.Count
    Set c = p2(r, 1)
    s = c.Formula
    c.Formula = Replace(s, c1.Address, c2.Address)
  Next r
  
  c2.Select 'Ready to change Name
End Sub

[chriswrcg]

I must admit, I don't really understand this. What do I change to make it work for me?

[xlnitwit]

Hi,

Why don't you just use the Find/Replace dialog to replace $B$1 with B1 in the first block of formula cells. Then copying the block down will automatically look at the relevant header row.

[chriswrcg]

I am building this to be shared between different departments as well as different sites in our company. This being the case I need it to be as self contained and automatic as possible. It is a lot easier to train someone to click on a macro or keyboard combination to activate the macro than the individual steps to accomplish a goal.

This is one page on a very large document and what I am coding now is the add new associate macro that will automatically add a new employee to the individual pages and create all the links to the other individual pages so it all works seamlessly.

Since each new employee will get their own "block" of 23 individual rows I need to be able to tell the workbook where to "look" for the new name and apply that location to all the links and formulas needed to make the page work right.

the standard find and replace dialog works the easiest but in this case is not the solution I am looking for.

[xlnitwit]

I'm afraid I do not follow. Once you have done that to the first template block, all the macro needs to do is copy the block and paste it as many times as required. No further changes will be necessary.