Adding column title

[reena]

This is regarding my old query only. I got the help through my earlie
post and after making some modifications, I have made written th
following code.


Sub test()

Dim Setup As Worksheet
Dim Inputf As Worksheet
Dim Output As Worksheet
Dim setrng As Range
Dim list As Range
Dim x As Long
Dim nwb As Workbook

Set Inputf = Workbooks("Input.xls").Worksheets(1)
Set Setup = Workbooks("Input.xls").Worksheets("Setup")
Set nwb = Workbooks.Add
Set Output = nwb.Worksheets(1)
Set setrng = Setup.Range("A1").CurrentRegion.Columns(1).Cells
x = 1
For Each list In setrng
Inputf.Columns(list.Value).Copy Destination:=Output.Columns(x)
x = x + 1
Next
nwb.SaveAs Filename:="Output.xls"
End Sub


Now I want to do some modifications in this. I want to check that, i
anyone has added the column title in the setup tab then it shuld tak
that perticulat title for the specified column. Else it will take th
original title of the column.
for eg., If I specify "a - Name", then it should take the title for th
column in "Output.xls" as "Name", and if I specify only "a", then i
should take the original title of the column.
How to do this

 

[Tom Ogilvy]

Sub test()

Dim Setup As Worksheet
Dim Inputf As Worksheet
Dim Output As Worksheet
Dim setrng As Range
Dim list As Range
Dim x As Long
Dim nwb As Workbook
Dim s as String, sName as String
Dim iloc as Long

Set Inputf = Workbooks("Input.xls").Worksheets(1)
Set Setup = Workbooks("Input.xls").Worksheets("Setup")
Set nwb = Workbooks.Add
Set Output = nwb.Worksheets(1)
Set setrng = Setup.Range("A1").CurrentRegion.Columns(1).Cells
x = 1
For Each list In setrng
iloc = Instr(1,list.Value,"-",vbTextCompare)
if iloc > 0 then
s = trim(left(list.value,iloc-1)
sName = trim(mid(list.value,iloc+1,255))
else
s = trim(list.value)
sName = ""
end if

Inputf.Columns(s).Copy Destination:=Output.Columns(x)
if iloc > 0 then Output.Cells(1,x).Value = sName
x = x + 1
Next
nwb.SaveAs Filename:="Output.xls"
End Sub

 

[reena]

Hi,

for this instad of writing "a - Name", if I want to specify "a in the
first column" and "Changed title in the other column" then how will I
modify the code?

Reena