I have a excel worksheet with bunch of columns.
Now I want to create a new excel sheet with only selected columns.
And user will be prompted to input the columns may be with a inputbox only
for onetime.
Example:
Let us say abc.xls has A to H columns.
User will input A C E
New excel sheet will have A, C and E columns only from abc.xls.

Ram,

Try the sub below.

HTH,
Bernie
MS Excel MVP

Sub PickColumnsToCopy()
Dim mySel As String
Dim myCols As Variant
Dim i As Integer
Dim myR As Range

mySel = InputBox("Which columns to use? " & Chr(10) & _
"(Use a space to separate letters!)")

On Error GoTo ErrHandler
myCols = Split(mySel, " ")

Set myR = Cells(1, myCols(LBound(myCols))).EntireColumn
For i = LBound(myCols) + 1 To UBound(myCols)
Set myR = Union(myR, Cells(1, myCols(i)).EntireColumn)
Next i

Sheets.Add
myR.Copy Range("A1")

Exit Sub
ErrHandler:
MsgBox "There was an error"

End Sub


"Ram" wrote in message
...
I have a excel worksheet with bunch of columns.
Now I want to create a new excel sheet with only selected columns.
And user will be prompted to input the columns may be with a inputbox only
for onetime.
Example:
Let us say abc.xls has A to H columns.
User will input A C E
New excel sheet will have A, C and E columns only from abc.xls.

May be quicker and easier for user to hold ctrl key and select any cell in
the column(s) desired and fire this.

Sub copyselectedcolumns()
Selection.EntireColumn.Copy
Sheets.Add
ActiveSheet.Paste
End Sub
--
Don Guillett
SalesAid Software

"Ram" wrote in message
...
I have a excel worksheet with bunch of columns.
Now I want to create a new excel sheet with only selected columns.
And user will be prompted to input the columns may be with a inputbox only
for onetime.
Example:
Let us say abc.xls has A to H columns.
User will input A C E
New excel sheet will have A, C and E columns only from abc.xls.

I will try today, Thank you

"Bernie Deitrick" wrote:

Ram,

Try the sub below.

HTH,
Bernie
MS Excel MVP

Sub PickColumnsToCopy()
Dim mySel As String
Dim myCols As Variant
Dim i As Integer
Dim myR As Range

mySel = InputBox("Which columns to use? " & Chr(10) & _
"(Use a space to separate letters!)")

On Error GoTo ErrHandler
myCols = Split(mySel, " ")

Set myR = Cells(1, myCols(LBound(myCols))).EntireColumn
For i = LBound(myCols) + 1 To UBound(myCols)
Set myR = Union(myR, Cells(1, myCols(i)).EntireColumn)
Next i

Sheets.Add
myR.Copy Range("A1")

Exit Sub
ErrHandler:
MsgBox "There was an error"

End Sub


"Ram" wrote in message
...
I have a excel worksheet with bunch of columns.
Now I want to create a new excel sheet with only selected columns.
And user will be prompted to input the columns may be with a inputbox only
for onetime.
Example:
Let us say abc.xls has A to H columns.
User will input A C E
New excel sheet will have A, C and E columns only from abc.xls.