Adding column title

This is regarding my old query only. I got the help through my earlier post and after making some modifications, I have made written the 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, if anyone has added the column title in the setup tab then it shuld take that perticulat title for the specified column. Else it will take the original title of the column.
for eg., If I specify "a - Name", then it should take the title for the column in "Output.xls" as "Name", and if I specify only "a", then it should take the original title of the column.
How to do this?

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

--
Regards,
Tom Ogilvy


"reena" <[email protected]> wrote in
message news:[email protected]...
>
> This is regarding my old query only. I got the help through my earlier
> post and after making some modifications, I have made written the
> 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, if
> anyone has added the column title in the setup tab then it shuld take
> that perticulat title for the specified column. Else it will take the
> original title of the column.
> for eg., If I specify "a - Name", then it should take the title for the
> column in "Output.xls" as "Name", and if I specify only "a", then it
> should take the original title of the column.
> How to do this?
>
>
> --
> reena
> ------------------------------------------------------------------------
> reena's Profile:
http://www.excelforum.com/member.php...o&userid=30440
> View this thread: http://www.excelforum.com/showthread...hreadid=501603
>

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