How do I implement ctrl + shift + down in VBA?

Question

How can I do a ctrl + shift + down in excel vba? I also wanted to know to then paste that as special values. Here is the code I currently have:

Sub CopyCol()

    Sheets("Sheet1").Range("B2").End(xlDown).Select.Copy

    Sheets("Sheet2").Range("B14").PasteSpecial xlPasteValues

End Sub

I am trying to copy everything in column B but not the whole column itself. For example, if the entries end at row 100 it should select cells B2 to B100 in the same way that ctrl+shift+down works.

After the cells are selected, I want to copy them and paste them as values in Sheet2 at cell B14. I know that putting .Select after (xlDown) would help me do that, but then I can't copy the cells. Also, pasting into the other sheet doesn't work either.

Answer 1

The method you would use is this:

Sheets("Sheet1").Range(Sheets("Sheet1").Range("B2"),Sheets("Sheet1").Range("B2").End(xlDown)).Copy

The Range() object works like this Range(Start Cell, End Cell)

So you anchor the first cell with Sheets("Sheet1").Range("B2").

And anchor the End Cell with Sheets("Sheet1").Range("B2").End(xlDown)).

Now another method with less typing is to use the With block:

With Sheets("Sheet1")
    .Range(.Range("B2"),.Range("B2").End(xlDown)).Copy
End with