Hi everybody,
I need to copy data from one sheet (active sheet) to another, say
"printdata". I need to find the next empty row in column A of "printdata" and
paste the information there. My problem is the active sheet will be changing
all the time but the destination is always the same sheet. How can I refer to
the last row in printdata without leaving the active sheet?
--
gaba :)

gaba,

This will paste from A1:A20 in the activesheet "printdata" as you specified:

Sub test()
Dim printdata_first_empty_row As Long
Dim paste_range As Range
Dim source_range As Range

printdata_first_empty_row = Worksheets("printdata").Range("A" &
Rows.Count).End(xlUp).Row + 1
Set paste_range = Worksheets("printdata").Range("A" &
printdata_first_empty_row)
Set source_range = ActiveSheet.Range("A1:A20") 'change for your situation
source_range.Copy _
Destination:=paste_range
End Sub

Note that if there's nothing in column A of "printdata" it will actually
determine the first empty row as row 2, so you'd have to error check for
that. And it will also error if A65536 (the last cell in column A) is not
empty.

hth,

Doug

"gaba" wrote in message
...
Hi everybody,
I need to copy data from one sheet (active sheet) to another, say
"printdata". I need to find the next empty row in column A of "printdata"
and
paste the information there. My problem is the active sheet will be
changing
all the time but the destination is always the same sheet. How can I refer
to
the last row in printdata without leaving the active sheet?
--
gaba :)

Thanks so much Doug, with little changes it made the trick!

"Doug Glancy" wrote:

gaba,

This will paste from A1:A20 in the activesheet "printdata" as you specified:

Sub test()
Dim printdata_first_empty_row As Long
Dim paste_range As Range
Dim source_range As Range

printdata_first_empty_row = Worksheets("printdata").Range("A" &
Rows.Count).End(xlUp).Row + 1
Set paste_range = Worksheets("printdata").Range("A" &
printdata_first_empty_row)
Set source_range = ActiveSheet.Range("A1:A20") 'change for your situation
source_range.Copy _
Destination:=paste_range
End Sub

Note that if there's nothing in column A of "printdata" it will actually
determine the first empty row as row 2, so you'd have to error check for
that. And it will also error if A65536 (the last cell in column A) is not
empty.

hth,

Doug

"gaba" wrote in message
...
Hi everybody,
I need to copy data from one sheet (active sheet) to another, say
"printdata". I need to find the next empty row in column A of "printdata"
and
paste the information there. My problem is the active sheet will be
changing
all the time but the destination is always the same sheet. How can I refer
to
the last row in printdata without leaving the active sheet?
--
gaba :)