Create a named cell dynamically
- by CaptMorgan
I have a workbook with 3 worksheets. 1 worksheet will have input values (not created at the moment and not needed for this question), 1 worksheet with several "template" or "source" tables, and the last worksheet has 4 formatted "target" tables (empty or not doesn't matter). Each template table has 3 columns, 1 column identifying what the values are for in the second 2 columns. The value columns have formulas in them and each cell is Named. The formulas use the cell Names rather than cell address (e.g. MyData1 instead of C2).
I am trying to copy the templates into the target tables while also either copying the cell Names from the source into the targets or create the Names in the target tables based on the source cell Names. My code below I am creating the target names by using a "base" in the Name that will be changed depending on which target table it gets copied to. my sample tables have "Num0_" for a base in all the cell names (e.g. Num0_MyData1, Num0_SomeOtherData2, etc). Once the copy has completed the code will then name the cells by looking at the target Names (and address), replacing the base of the name with a new base, just adding a number of which target table it goes to, and replacing the sheet name in the address.
Here's where I need help. The way I am changing that address will only work if my template and target are using the same cell addresses of their perspective sheets. Which they are not. (e.g. Template1 table has value cells, each named, of B2 thru C10, and my target table for the copy may be F52 thur G60). Bottom line I need to figure out how to copy those names over with the templates or name the cells dynamically by doing something like a replace where I am incrementing the address value based on my target table #...remember I have 4 target tables which are static, I will only copy to those areas. I am a newbie to vba so any suggestions or help is appreciated.
NOTE: The copying of the table works as I want. It even names the cells (if the Template and Target Table have the same local worksheet cell address (e.g. C2)
'Declare Module level variables
'Variables for target tables are defined in sub's for each target table.
Dim cellName As Name
Dim newName As String
Dim newAddress As String
Dim newSheetVar
Dim oldSheetVar
Dim oldNameVar
Dim srcTable1
Sub copyTables()
newSheetVar = "TestSheet"
oldSheetVar = "Templates"
oldNameVar = "Num0_"
srcTable1 = "TestTableTemplate"
'Call sub functions to copy tables, name cells and update functions.
copySrc1Table
copySrc2Table
End Sub
'****there is another sub identical to this one below for copySrc2Table.
Sub copySrc1Table()
newNameVar = "Num1_"
trgTable1 = "SourceEnvTable1"
Sheets(oldSheetVar).Select
Range(srcTable1).Select
Selection.Copy
For Each cellName In ActiveWorkbook.Names
'Find all names with common value
If cellName.Name Like oldNameVar & "*" Then
'Replace the common value with the update value you need
newName = Replace(cellName.Name, oldNameVar, newNameVar)
newAddress = Replace(cellName.RefersTo, oldSheetVar, newSheetVar)
'Edit the name of the name. This will change any formulas using this name as well
ActiveWorkbook.Names.Add Name:=newName, RefersTo:=newAddress
End If
Next cellName
Sheets(newSheetVar).Select
Range(trgTable1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
PING
