How to address a defined column

[dburks1]

I am moving columns from one worksheet to another. In doing so I am using the scrip to copy the entire column.

ColumnsToCopy = Array("State", "City", "Address", "Phone")
Set SourceSht = ActiveSheet
Set newWbk = Workbooks.Add
Set NewSht = newWbk.Sheets(1)
NewSht.Name = "Students"

Where I get stuck is trying to address the particular column and not the range. I know that I can address a column by a range but I would rather address it by the column as the order of them might change in the future.

How would I address the "State" Column by sorting descending.

 

[Bong]

for each c in NewSht.columns
if c.cells(1).value="State" then colNo=c.column
next

_________________
Bob Rashkin

 

[SkipVought]

Hi,

    Set SourceSht = ActiveSheet 
    Set newWbk = Workbooks.Add
    Set NewSht = newWbk.Sheets(1)
    NewSht.Name = "Students"
   
   iCol = 1
   for each c in sourcesheet.columns
      with c
         select case c.Cells(1).value
            case "State", "City", "Address", "Phone"
               .copy
               NewSht.cells(1,iCol).pastespecial xlpasteall
               icol=icol+1
         end select
      end with
   next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}