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

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

On Sep 17, 10:16 pm, Lightjag
wrote:
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

Hi Lightjag

See also
http://www.rondebruin.nl/copy1.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Lightjag" wrote in message ...
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