Selecting a column

[L.White]

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

 

[Guest]

Mostly likely if it is failing on the find command then it is not finding the
data you supplied.
Ben

 

[L.White]

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

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

 

[Norman Jones]

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))

 

[Guest]

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 & "" & 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

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

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

 

[Norman Jones]

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