Macro to Paste at next blank row

L

Lightjag

This Macro copies from 1 tab to another tab in a workbook. The tab that the
data is being pasted into, keeps pasting to the same row. I want the data
pasted to the next empty row.
-----------------------------------------------
Sub copydata()
'
' copydata Macro
'
' Keyboard Shortcut: Ctrl+t
'
Application.Goto Reference:="R7C1"
Rows("7:7").Select
Selection.Copy
ActiveSheet.Next.Select
Application.Run "BLPLinkReset"
Application.Goto Reference:="R8C1"
Selection.End(xlDown).Select
Range("A11").Select <----- how do I change this to = next
blank row?
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Drop").Select
Application.Run "BLPLinkReset"
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
End Sub
 
J

jhyatt

Try this replace "B5" with the cell you want to start with

Range("b5").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
 
P

Per Erik Midtrød

This Macro copies from 1 tab to another tab in a workbook. The tab that the
data is being pasted into, keeps pasting to the same row. I want the data
pasted to the next empty row.
-----------------------------------------------
Sub copydata()
'
' copydata Macro
'
' Keyboard Shortcut: Ctrl+t
'
Application.Goto Reference:="R7C1"
Rows("7:7").Select
Selection.Copy
ActiveSheet.Next.Select
Application.Run "BLPLinkReset"
Application.Goto Reference:="R8C1"
Selection.End(xlDown).Select
Range("A11").Select <----- how do I change this to = next
blank row?
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Drop").Select
Application.Run "BLPLinkReset"
Application.CutCopyMode = False
Application.Goto Reference:="R1C1"
End Sub

I am quite sure someones going to find a better solution, but this one
should work:
Replace range("A11").select with:
Range("A65536").End(xlUp).Select ' If you use Excel 2007 you should
replace A65536 with 1048576
r = Selection.Row + 1
Range("A" & r).Select

Per Erik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top