Macro to find and replace specific strings in a cell link by using cell references

Capture.JPG
Screenshot of example rows

Spreadsheet file: Test Training Sheet.xlsx

This is the macro that I first recorded with relative references:

Please Login or Register  to view this content.

My goal is to have, say, Row 1 be the example row that would be copied into all other rows. The only difference would be the name in Columns B and C. This name corresponds to the links in the cells like so:

Please Login or Register  to view this content.

I would want the macro or code to look through and change "Billy Bob" to whatever is entered into the B cell of that row. The name in each row is linked to it's own spreadhsheet. All the cells in the row pull from that individual spreadsheet, so their links need to correspond to the name in the B cell.

However, I can't get my macros and vba code to work and would greatly appreciate any assistance or advice!

try amending the following code to suit your needs:

Please Login or Register  to view this content.

Thank you so much! However, when I run the macro, nothing happens. Should I be selecting certain cells before running? I ran it on the whole sheet and on a selection of all the columns separately and both times nothing occurred.

Additionally, the $E$2 in the link is only for that cell/column. All the other columns of D-M reference other cells in the linked sheets such as $I$2.

Would there be a lot of editing required to accommodate this in the code?

However, when I run the macro, nothing happens.

Trust you have saved the file as XLSM (macro-enabled workbook) and not XLSX format before running the macro. Let me know if it still doesn't run.

All the other columns of D-M reference other cells in the linked sheets such as $I$2.

For this, populate a few rows with the expected result so one can see a progression.

I read your sample workbook
I need explanation, for ex:

In cell D3, the formula is: ='G:\DINING\64 Degrees Student Manager\Training\Students\[64 Template - Copy.xlsx]Sheet1'!$E$2
Do you want it to become: ='G:\DINING\64 Degrees Student Manager\Training\Students\[Billy Bob.xlsx]Sheet1'!$E$2

Correct. Billy Bob because it is in the corresponding B cell for that row. In the next row below, it would be Joanne Sue. Each name in Column B, such as Billy Bob, corresponds to a spreadsheet of the same name, Billy Bob.xlsx. That way, each named spreadsheet is linked to the cells of that row.

Ok, another question
In cell C3, the formula is: =HYPERLINK("G:\DINING\64 Degrees Student Manager\Training\Students\64 Template - Copy.xlsx"&B3&"'!A1";B3)
Do you want it to become:
=HYPERLINK("[G:\DINING\64 Degrees Student Manager\Training\Students\Billy Bob.xlsx]Billy Bob!A1";B3)
it means there's a sheet name Billy Bob in the workbook Billy Bob
or
=HYPERLINK("[G:\DINING\64 Degrees Student Manager\Training\Students\Billy Bob.xlsx]sheet1!A1";B3)

That was the result of my tinkering. In my actual workbook, the formula is as follows:

=HYPERLINK("G:\DINING\64 Degrees Student Manager\Training\Students\Billy Bob.xlsx",B3)

Try this :
Macro 'insertAll' is to insert cell refrencere in column D to M in all rows that already has data (Names)

Macro 'insertOne' is to insert cell refrencere in column D to M in one row, that is if you type new data in column B. Notes: after you finish typing the name use right arrow (not enter) then run the macro, it's because the macro needs the cursor still in that row.
You can assign this macro to a keyboard shortcut, in case you need it frequently.

For hyperlink in column C, copy this formula to C3 then copydown.

Please Login or Register  to view this content.

You can change the path of the workbook if you need to (see the code line in red).
Please check the sequence of cell reference in column D to M, in case I got it wrong.

Please Login or Register  to view this content.

So, instead of Billy Bob.xlsx, it inserts Billy Bob. xlsx which causes the error.

Is it 'Billy Bob. xlsx' or 'Billy Bob .xlsx'? I guess it's 'Billy Bob .xlsx'

However, when I run the macro Insert All, a dialog box pops up asking me to update values

I modified the macro to address both issue, let me know how it works.

Please Login or Register  to view this content.

Thank you so much for your help.

Upon further inspection, I realized that the problem rows had spaces after the name entries in Column B. I realized that the macro was functioning correctly, but the name that it was putting into the links had the space in it.

However, there was one name that just would not work. The file it referenced existed and all the names were written correctly with no spaces. The only way it worked was if I re-created the entire row after deleting the original.

Any ideas as to why this happened?

I don't know why. And it's only happen to one name? What's the name?