new worksheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
I will try today, Thank you

Bernie Deitrick said:
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
Click to expand...
 
Back
Top