Excel VBA Copying and Pasting into Next Blank Row in Another Sheet

Question

I'm trying to record a macro using keystrokes only and am coming across an issue on my final keystroke as it is not doing what I expect it to be. A little background in what I am doing - I am working with growing data and a rolling graph to graph the last 4 rows of data. I am trying to copy data from one sheet and paste it onto the next empty row on my table in another sheet. Because I'm using keystrokes, I expected the macro to record the number of clicks but this is not what it's doing. Instead it pastes the data right over the exact cell that the macro was recorded in and not the empty cell below it. For example, I have data in C19 already. When I run the macro, I expect it to paste the new data into C20 but instead it pastes over C19. I think I need to add/edit my VBA so that it will paste the new data into the next empty row in another sheet. I hope this made sense. Any help will be much appreciated!

Thank you!!

Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Range("C3").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[-1]), MONTH(RC[-1])+1, DAY(RC[-1]))"
Range("C3").Select
Selection.Copy
Range("B3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Range("C3").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("B85:K146").Select
Selection.Copy
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
Range("B9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
Range("A1").Select
ActiveSheet.Next.Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Range("E71:G71").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
ActiveSheet.Next.Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
Range("C5").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("C19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

End Sub

using keystrokes only please don't do that. Edit: use logic and code it that way ^_^ — findwindow
Also, before you try to tackle the main code issue, please read through How to avoid using .Select. This is a great place to start, especially if you use the macro recorder. You'll see pretty quickly, that lots of your code can straight up be removed. Learn to work directly with the data instead. — BruceWayne

Absinthe Absinthe · Accepted Answer · 2016-06-28T19:04:38

As others have commented it's best if you read up on how to code directly rather than record a macro. As a quick fix though this should help:

Replace this:

Range("C19").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

with this:

Cells(Range("C1000000").End(xlUp).Row + 1, 3).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False

What the .End(xlUp) does is to go from row 1,000,000 upwards looking for the first cell that contains something. The .Row gets the row it's on and the + 1 means we want the next row. Cells is another way of expressing a Range, look up Range and Cell on-line, it will help you on your way.

Excel VBA Copying and Pasting into Next Blank Row in Another Sheet

1 Answers