Paste in first available cell

Question

I am trying to create a code that allows me to paste a selected range of data and paste it into Book2 in the first blank cell in Column A, starting from A1. This is what I've got so far:

Sub Macro 1 ()        
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select            
        Selection.Copy

        Windows("Book2.xlsm").Activate
        **CurrentRow = Range("A1").End(xlDown).Offset(1, 0).Row**
        ActiveSheet.Paste
    End Sub

I believe the trouble is the line with asterisks (**).Can someone help me rewrite this line/code so the copied data can paste in the first available cell from A1 down? (Up won't work since i have filled in cells further down the chart). Right now the code is pasting the data in whatever cell is selected :(

Thank you for your help everyone.

You haven't even specified the worksheet. See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. — , Apr 05 '16 at 17:13

score 1 · Accepted Answer · answered Apr 05 '16 at 18:38

1

Try this out:

Dim book2 As Excel.Window
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Workbooks("Book2").Activate
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste

answered Apr 05 '16 at 18:38

michwalk

323
1
5

1

This worked perfectly @michwalk Thank you very much! – BMRobin Apr 05 '16 at 19:16

score 0 · Answer 2 · answered Apr 06 '16 at 00:53

0

You could work more directly with ranges (without Select) as below:

Sub Better()

Dim Wb As Workbook
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range(ActiveCell, ActiveCell.End(xlToRight))
Set rng1 = Range(rng1, rng1.End(xlDown))

On Error Resume Next
Set Wb = Workbooks("book2.xlsm")
On Error GoTo 0

If Wb Is Nothing Then Exit Sub

Set rng2 = Wb.Sheets(1).Columns(1).Find("*", Wb.Sheets(1).[a1], , , xlByRows, xlPrevious)
If rng2 Is Nothing Then Set rng2 = Wb.Sheets(1).[a1]
rng1.Copy rng2.Offset(1, 0)

End Sub

answered Apr 06 '16 at 00:53

brettdj

54,857
16
114
177

Thank you @brettdj Do I have to do something with the green asterisk? – BMRobin Apr 26 '16 at 15:57
Oh, I pasted the name of the document. It worked. But it pasted it into A1 and not the first available cell in column A starting from the top :( @brettdj Is there something we can change in the code? Thank you! – BMRobin Apr 26 '16 at 16:10

Paste in first available cell

2 Answers2