hi, seems to work 99%, does all the functions but gets to end and get a
Runtime Error:
This operation requires the merged cells to be identically sized.

it selects area to left of every thing doing, between column B / DT.
(CF:CO)..
anyways, have some cells in header merged (must have), is there a way to
skip that debug situation? rates.. have other work.

"JLatham" wrote:

Here is the modified code, I used cells A1 through F1 on the same sheet to
put the various column references, but you could use other cells as long as
their addresses remain constant, you could even put them on another sheet.
That's explained in the code below.
I hope this helps with the situation.

Private Sub CommandButton1_Click()
'to prevent confusion with code like Range(Range("A1"))
'we will move our information from cells A1 through F1
'into variables that we will use in the code below
'note that within the cells on the worksheet, the
'double quote marks are NOT used, so in cell A1
'you would actually just enter DN6 not "DN6"
'and in B1 you would enter DU:DU, not "DU:DU"
'changing the contents of those cells will change
'the way the code operates.
Dim testCellAddress As String ' will hold "DN6" from A1
Dim singleColumnID As String ' will hold "DU:DU" from B1
Dim groupOneColumnID As String ' will hold "EE:EY" from C1
Dim groupTwoColumnID As String ' will hold "FE:FV" from D1
Dim groupThreeSourceID As String ' will hold "EC:ED" from E1
Dim groupThreeDestinationID As String ' will hold "FE:FF" from F1
'get the values from the active sheet
'if the cells are on a sheet where columns may be added or
'deleted, then they need to be in cells to the left of the
'any column that might get moved. In other words, these
'address must remain stable. You could put the information
'on another sheet and reference them in a fashion similar to
' testCellAddress=Worksheets("AnotherSheetName").Ran ge("A1")
testCellAddress = Range("A1") ' .Value is implied
singleColumnID = Range("B1")
groupOneColumnID = Range("C1")
groupTwoColumnID = Range("D1")
groupThreeSourceID = Range("E1")
groupThreeDestinationID = Range("F1")

If Range(testCellAddress).Value = "Z" Then
'1 col: copy Paste-Values to left 1 col
Columns(singleColumnID).Select
Selection.Copy
Range(singleColumnID).Offset(0, 1).Select ' 1 column to the right
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(groupOneColumnID).Select
Selection.Copy
Range(groupOneColumnID).Offset(0, 1).Select ' 1 column to the right
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
'20 col: (10 sets of 2), COPY: Paste-Values to right 2 cols
Columns(groupTwoColumnID).Select
Selection.Copy
Range(groupTwoColumnID).Offset(0, 2).Select ' 2 columns to the right
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
'double col: (1 set of 2), COPY: Paste-Values to different section
Columns(groupThreeSourceID).Select
Selection.Copy
Range(groupThreeDestinationID).Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End If
End Sub


"Nastech" wrote:

hi, thanks for responses. sorry if questions sometimes vague. am not sure
what is pertinent vs. posting too much. (work on advanced items?.. slower
with some area's novice: spend much time to develope..).

:) not even sure if your answers apply.. new to scripting.. macro's, you
may need to tell me if your anwers apply to what doing, as not sure. thanks.


Will include script below that references, mostly columns. work-around is
for some kind of INDIRECT() reference to desired cells, from cells that will
not be moved, i.e. programming will never have to change.. ?? (not sure on
that), by use of either just typing the COLUMN LETTER reference in cells
listed (B2 C2, B3 C3 etc), OR with next formula use as a more "DYNAMIC"
response:

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

gets: DU:DU
so if need any else such as quotes.. around "DU:DU", I can add to that
formula as well.

IF YOUR ANSWERS DO the same thing.. or apply to what doing you can let me
know, will work on but take some time for me to try.

PROBLEM: when add/ remove columns, have to manually change script.
SUBSTITUTE formula is work around.

Script using is:


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






"JLatham" wrote:

If I understand you correctly, this concept should work.

Lets say that the column identifier you want to change is on a worksheet
named Sheet1 and it is in cell A1. Then your code can do something like
either of these examples:

Dim myColumns As String
myColumns = ThisWorkbook.Worksheets("Sheet1").Range("A1")
'assuming that cell A1 contained B:C then you dould do this

'you must activate the proper sheet first
Worksheets("Sheet2").Activate
Columns(myColumns).Select

and that would select columns A:C on Sheet2

You could even write it without "myColumns" this way
Worksheets("Sheet2").Activate
ThisWorkbook.Worksheets("Sheet1").Range("A1")
Columns(ThisWorkbook.Worksheets("Sheet1").Range("A 1")).Select

Hope this helps you with your problem.

"Nastech" wrote:

hi, is there a way to modify the reference to columns,
to be from a different single cell, such as INDIRECT..
within a macro / script? Thanks

the type of lines I want to reference a

Range("A1").Value
Columns("B:C").Select
Range("D:E").Select

Intersect(Me.Range("F:G"),
With Me.Cells(.Row, "H")