Dynamic copy and pasting using ctrl+shift+down function aka Selection.End(x1down)

Question

I am attempting to move ranges (of varying lengths) between two workbooks.

Windows("Comp1.xlsx").Activate 'Open sheet to pull data from
Range("E2").Select 'Starting point is the same every time
Range(Selection, Selection.End(xlDown)).Select 'Select all data below
Application.CutCopyMode = False
Selection.Copy 'Copy range
Windows("Comps Proto.xlsm").Activate 'Sheet to be pasted into
Range("K12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False 'Paste data into new sheet

In order to repeat this function for the next workbook, I need to move off the pasted selection. I have tried everything, including offset, and Application.CutCopyMode = False. Doesn't work.

See below picture: The first paste cycle ends with that range selected. I want to move to the cell labeled and circled Start. This is where the next range will be pasted in the same fashion as above, rinse and repeat

Which book has the code and which book has the data that is going to be copied to other book? You do not need to `.Select` anything to `Copy/Paste` — urdearboy, Aug 22 '18 at 21:12
"Comp1.xlsx" is the book with the data. The picture shows the book where it will be pasted. The reason I am using Select is because I want it pasted in a particular format. — yaboySH, Aug 22 '18 at 21:26
this is not how VBA works, you don't need to immitate the UI, you can do this easily with VBA, but your question does not say anything specific and it is not clear enough — Ibo, Aug 22 '18 at 21:27
Comps Proto hosts the code. I would like the macro to copy ranges with fixed starting points and variable ending points and then paste the selections into the Comps Proto in a particular format. The format is pretty straight forward, but I just want a row to be inserted after each paste. — yaboySH, Aug 22 '18 at 21:31
You can find many answers here on how to find dynamic last row here. All you have to do to leave a blank is offset by 1. See [here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — urdearboy, Aug 22 '18 at 21:40

score 0 · Answer 1 · answered Aug 23 '18 at 13:51

The main issue I was trying to resolve was getting the cursor to click off a pasted range, so that I could continue. I found the code below to be helpful. This is the original post on another forum: https://superuser.com/questions/342772/how-do-i-move-the-selection-down-one-row-in-excel-2007/342835

Dim ColNumber As Integer

ColNumber = Selection.Column
Range("K" & CStr(ColNumber)).Select 'Click off the pasted values
ActiveCell.End(xlDown).Select 'Equivalent of ctrl+down
ActiveCell.End(xlDown).Select 'Moves down through the range in picture
ActiveCell.Offset(2, 0).Select 'Moves selection to the "Start" point in picture above

score 0 · Answer 2 · answered Aug 23 '18 at 17:49

Judging by your range location, it has free cells across all borders. This allows to use handy CurrentRegion property. In my code I assume you have 5 such blocks to copy. Feel free to change the place to copy to.

Sub MoveCells()
    Dim x%, rng As Range
    Set rng = [E5].CurrentRegion
    '// Copy 5 blocks of cells
    Do
        '// Change target cell to the one you need
        rng.Copy Sheets(2).Cells(1, x + 1)
        '// Here we locate last cell in block of cells, offset two cells down
        '// and select CurrentRegion again
        Set rng = rng(rng.Cells.Count).Offset(2).CurrentRegion
        x = x + 1
    Loop While x <= 5
End Sub

Dynamic copy and pasting using ctrl+shift+down function aka Selection.End(x1down)

2 Answers2