Find First empty cell at end of Column A

[DG]

I am copying a range of data from one sheet1 to the end of sheet2. Sheet1
will keep changing and I always want to append it to the bottom of sheet2.

Range("A1").End(xldown).Select will get me to the last USED cell. But how
do I go one more?

Also, I used Range(Range("A2").End(xlToRight),
Range("A2").End(xlDown)).Copy to copy the data. One I select the next unused
cell in sheet2 how do I paste?

Dan

 

[Nick Hodge]

Dan

Do your copying here.... then

Worksheets("Sheet2").Range("A1").End(XlDown).Offset(1,0).Select

Selection.Paste

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Don Guillett]

No need to SELECT.

Range("A1").End(xldown).offset(1)=1 'whateveryouwanthere
or this one liner
range("b2:b22")copy range("a1").end(xldown).offset(1)

 

[Dave Peterson]

range("a1").end(xldown).offset(1,0).select

or maybe

with activesheet
.cells(.rows.count,"A").end(xlup).offset(1,0).select
end with

To come from the bottom up.

 

[Dave Peterson]

range("a1").end(xldown).offset(1,0).select

or maybe

with activesheet
.cells(.rows.count,"A").end(xlup).offset(1,0).select
end with

To come from the bottom up.

 

[DG]

Thanks,

That worked except the Selection.Paste didn't. However ActiveSheet.Paste
worked.

I only have one other problem. The paste brought over the formating
(borders) how do I use ActiveSheet.PasteSpecial to just past the values?

Dan

 

[Nick Hodge]

Dan

I'm not having a good day, activesheet was what I meant, as most have
pointed out this can also be done in a single operation (Pseudo code)

Range.Copy Destination:=Worksheets("Sheet2").Range("A1")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Don Guillett]

To just get the values without formatting or pasting. NO copying and NO
pasting.

range("a2:a22").value=range("b2:b22").value