INDIRECT Question..
 

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

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.. 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