Moving to the end of a Column and Pasting (Excel) 2

[stephenj789]

I have a simple macro that moves to the first empty cell at the end of a column, then is supposed to paste the selection to it.

Employee #
012
013
014
015
(Empty Space)

What I would like the macro to do is move to the empty space and paste a new set of employee numbers. The output would look like this:

Employee #
012
013
014
015
016
017
018

Here is the macro I am trying to get to work (A range of employee numbers will already selected when this macro is initiated):

Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste

However, when I run the macro, I lose the selection and nothing pastes (although the correct cell is activated). I think this is because I am using "Select" in my commands (which kills any previous selections). Does anybody know of a way I can move to the first empty cell in a column and paste a selection?

 

[SkipVought]

Hi,

Not a good idea to overuse the Activate and Select methods. Most Excel VBA can be done WITHOUT them.

Let's assume that your list is in column A beginning in A1

    [A1].End(xlDown).Offset(1, 0).Paste

In fact your copy 'n' paste can often be done in ONE statement like this. Lets say that you are copying from Sheet1 to Sheet2...

'copies a range from sheet1 to the next empty row in sheet2
With Sheets("Sheet1")
    .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)).Copy _
        Sheets("Sheet2").[A1].End(xlDown).Offset(1, 0).Paste
End With

Check out...

How Can I Make My Code Run Faster? faq707-4105




Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[Gavona]

Either of the following. Assumes that your list starts in cell A1. (If this cell keeps moving then use a named range instead.)

Sub mytest()
Selection.Copy
Range("a1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End Sub

Sub mytest2()
Selection.Copy
Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End Sub

Thanks,

Gavin

 

[stephenj789]

I have got it working now. Hopefully someday I will be able to help people with VBA or other programming/computing questions.

Thanks for your help SkipVought/Gavona