hi, I have some script recently had help making.
for ranges / cells described, would like to use an INDIRECT reference in
script, to cells that will not be moved (so as to reference cells that might
be moved)

is that possible? the cells I am referencing using formula's that follow.
correlation is listed below, but imagine that I can insert where needed if I
can get the correct function needed.. thanks

cell B1 contains:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$DN$6),"$"," "),"","")

cell B2 / C2 contains:
=SUBSTITUTE(SUBSTITUTE(CELL("address",$DU2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DU2),"$",""),ROW(),"")

=SUBSTITUTE(SUBSTITUTE(CELL("address",$DT2),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$DT2),"$",""),ROW(),"")

which all output, in sheet, as follows (related to script using below)

DN6 in cell B1
DU:DU DT:DT B2 C2
EE:EY EF1 B3 C3
FE:FV FG:FX B4 C4
EC:ED FE:FF B5 C5

CK:CO CF B6 C6
CW:CW CG B7 C7


SCRIPT:

Option Explicit
Private Sub CommandButton1_Click()
If Range("DN6").Value = "Z" Then
'1 col: copy Paste-Values to left 1 col
Columns("DU:DU").Select
Selection.Copy
Range("DT:DT").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
'22 col: (main, 21 col back up), COPY: Paste-Values to right 1 col
Columns("EE:EY").Select
Selection.Copy
Range("EF1").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
'20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols
Columns("FE:FV").Select
Selection.Copy
Range("FG:FX").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
'double col: (1 set of 2), COPY: Paste-Values to different section
Columns("EC:ED").Select
Selection.Copy
Range("FE:FF").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Target.Row < 130 Then Exit Sub
If Me.Cells(.Row, "A").Value = "." Then Exit Sub
'add "+" to blank spaces col A:
If Not Intersect(Me.Range("a:a"), .Cells) Is Nothing Then
Application.EnableEvents = False
.Value = Replace(.Value, " ", "+")
Application.EnableEvents = True
End If
'make column changes:
If Not Intersect(Me.Range("CK:CO"), .Cells) Is Nothing Then
Application.EnableEvents = False
'Destination:
With Me.Cells(.Row, "CF")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
'make column changes:
If Not Intersect(Me.Range("CW:CW"), .Cells) Is Nothing Then
Application.EnableEvents = False
'Destination
With Me.Cells(.Row, "CG")
.NumberFormat = "dd"
.Value = Now
End With
Application.EnableEvents = True
End If
End With
End Sub