I am having a user select a name out of a list. Then click on the button
below. The click should then find the matching name from the sheet. Select
the entire column from the active cell down and move it to another sheet for
archiving. I have the following so far.

Private Sub CommandButton2_Click()
' Remove Employee
Dim myEmp As String

' Match up the name and the column
myEmp = Range("L1")
Sheets("Current").Range("D4").Select
Cells.Find(What:=myEmp, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

' Select the entire column
ActiveCell.Select
ActiveCell.EntireColumn.Select

' Cut the column and place it in the Previous sheet
Selection.Cut
Sheets("Previous").Select
Range("IA1").End(xlToLeft).Select
ActiveCell.Paste

End Sub

For some reason the macro fails right after the sheet select. I have tried a
few different options on the range, but nothing is working for me. How would
one of you write this to take the cut material and paste it into the first
empty column from the left? Thanks to all of you for your help
LWhite

Mostly likely if it is failing on the find command then it is not finding the
data you supplied.
Ben
--
When you lose your mind, you free your life.


"L.White" wrote:

I am having a user select a name out of a list. Then click on the button
below. The click should then find the matching name from the sheet. Select
the entire column from the active cell down and move it to another sheet for
archiving. I have the following so far.

Private Sub CommandButton2_Click()
' Remove Employee
Dim myEmp As String

' Match up the name and the column
myEmp = Range("L1")
Sheets("Current").Range("D4").Select
Cells.Find(What:=myEmp, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

' Select the entire column
ActiveCell.Select
ActiveCell.EntireColumn.Select

' Cut the column and place it in the Previous sheet
Selection.Cut
Sheets("Previous").Select
Range("IA1").End(xlToLeft).Select
ActiveCell.Paste

End Sub

For some reason the macro fails right after the sheet select. I have tried a
few different options on the range, but nothing is working for me. How would
one of you write this to take the cut material and paste it into the first
empty column from the left? Thanks to all of you for your help
LWhite

The failuer occurs when I try to select the empty cell in the "Previous"
sheet.

Range("IA1").End(xlToLeft).Select

If I enter a actual cell address in here I still get an error. Meaning that
if I try to use the line of code:

Range("A1").Select

I still receive Run-time error '1004' Select method of Range class failed. I
don't understand why this is failing. If I record a macro that is me
selecting a column, moving to the other sheet and then pasting the column I
would get the following code:

Columns("AJ:AJ").Select
Selection.Cut
Sheets("Previous").Select
Range("A1").Select
ActiveSheet.Paste

LWhite

"ben" (remove this if mailing direct) wrote in message
...
Mostly likely if it is failing on the find command then it is not finding
the
data you supplied.
Ben
--
When you lose your mind, you free your life.

Hi L,

Try replacing:

' Cut the column and place it in the Previous sheet
Selection.Cut
Sheets("Previous").Select
Range("IA1").End(xlToLeft).Select
ActiveCell.Paste

with:

ActiveCell.EntireColumn.Cut
ActiveSheet.Paste (Sheets("Previous"). _
Range("IA1").End(xlToLeft))

---
Regards,
Norman



"L.White" wrote in message
...
I am having a user select a name out of a list. Then click on the button
below. The click should then find the matching name from the sheet. Select
the entire column from the active cell down and move it to another sheet
for
archiving. I have the following so far.

Private Sub CommandButton2_Click()
' Remove Employee
Dim myEmp As String

' Match up the name and the column
myEmp = Range("L1")
Sheets("Current").Range("D4").Select
Cells.Find(What:=myEmp, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

' Select the entire column
ActiveCell.Select
ActiveCell.EntireColumn.Select

' Cut the column and place it in the Previous sheet
Selection.Cut
Sheets("Previous").Select
Range("IA1").End(xlToLeft).Select
ActiveCell.Paste

End Sub

For some reason the macro fails right after the sheet select. I have tried
a few different options on the range, but nothing is working for me. How
would one of you write this to take the cut material and paste it into the
first empty column from the left? Thanks to all of you for your help
LWhite

Hi,
Is what you want? I altered the selection to be copied to be Active
cell down - NOT entire column (as this what you said you wanted). I stored in
"Previous" starting column A and moving to right i.e. A, B, C etc

HTH

Sub CommandButton2_Click()
' Remove Employee
Dim myEmp As String

' Match up the name and the column
myEmp = Range("A1")

With Worksheets("Current")

Range("D4").Select
Cells.Find(What:=myEmp, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

' Select the entire column
ActiveCell.Select
Set rng = Range("D" & ActiveCell.Row & ":D" & Cells(Rows.Count,
"D").End(xlUp).Row)

' Cut the column and place it in the Previous sheet
Selection.Cut

End With

Sheets("Previous").Select
With Sheets("Previous")
col = Application.CountA(.Range("a1:ia1")) + 1 'Find next blank
column starting at "A"
rng.Copy .Range(Cells(1, col), Cells(1, col))
End With

End Sub


HTH


"L.White" wrote:

The failuer occurs when I try to select the empty cell in the "Previous"
sheet.

Range("IA1").End(xlToLeft).Select

If I enter a actual cell address in here I still get an error. Meaning that
if I try to use the line of code:

Range("A1").Select

I still receive Run-time error '1004' Select method of Range class failed. I
don't understand why this is failing. If I record a macro that is me
selecting a column, moving to the other sheet and then pasting the column I
would get the following code:

Columns("AJ:AJ").Select
Selection.Cut
Sheets("Previous").Select
Range("A1").Select
ActiveSheet.Paste

LWhite

"ben" (remove this if mailing direct) wrote in message
...
Mostly likely if it is failing on the find command then it is not finding
the
data you supplied.
Ben
--
When you lose your mind, you free your life.

Hi L,

In fact, to paste into the next empty cell, this needs an offset:

Sub Temp01()
ActiveCell.EntireColumn.Cut
ActiveSheet.Paste (Sheets("Previous"). _
Range("IA1").End(xlToLeft).Offset(, 1))
End Sub


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi L,

Try replacing:

' Cut the column and place it in the Previous sheet
Selection.Cut
Sheets("Previous").Select
Range("IA1").End(xlToLeft).Select
ActiveCell.Paste

with:

ActiveCell.EntireColumn.Cut
ActiveSheet.Paste (Sheets("Previous"). _
Range("IA1").End(xlToLeft))

---
Regards,
Norman



"L.White" wrote in message
...
I am having a user select a name out of a list. Then click on the button
below. The click should then find the matching name from the sheet.
Select
the entire column from the active cell down and move it to another sheet
for
archiving. I have the following so far.

Private Sub CommandButton2_Click()
' Remove Employee
Dim myEmp As String

' Match up the name and the column
myEmp = Range("L1")
Sheets("Current").Range("D4").Select
Cells.Find(What:=myEmp, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

' Select the entire column
ActiveCell.Select
ActiveCell.EntireColumn.Select

' Cut the column and place it in the Previous sheet
Selection.Cut
Sheets("Previous").Select
Range("IA1").End(xlToLeft).Select
ActiveCell.Paste

End Sub

For some reason the macro fails right after the sheet select. I have
tried a few different options on the range, but nothing is working for
me. How would one of you write this to take the cut material and paste it
into the first empty column from the left? Thanks to all of you for your
help
LWhite