7

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.

Improve this question
2
  • 1
    Record a macro, and then view the source code Excel generates. It should give you everything you need. Commented Feb 22, 2016 at 17:01
  • Also, it's not quite your question, but you can skip copy/paste (thus avoiding the Clipboard) by simply setting two ranges' values equal. (This works well when you want only the values). i.e. your code in your post could also be written as [range you want to fill] = [range whose values you want to copy], or Sheets("Sheet2").Range("B14").Value = Sheets("Sheet1").Range("B2").Value Commented Feb 22, 2016 at 18:02

1 Answer 1

Reset to default
17

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@Moe - This is an excellent time perhaps to also learn to use With to cut down on the repetition, as (IMO) it makes it a little hard to read when you have Sheets("Sheet1") // Sheets("Sheet1").Range("B2") repeated so much. Edit: Ah, Scott had the idea as I typed my comment. Quick q: If you did With Sheets("Sheet1").Rnage("B2"), then could you just to .Range(,.End(xldown)).Copy? Or do you actually need some references in there?
Perfect! i added the second code as well to paste and it worked perfectly! thank you!
@BruceWayne yup was typing that as you commented.
@Moe In about ten minutes you will be able to mark this as correct, please do so, by clicking on the grey/green check mark by the answer. It is something only you can do.
@ScottCraner Got you buddy!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.