Macro code for dropping to next empty cell

J

JB2010

Hi

I am trying to get a macro to copy data from one cell & paste it onto the
bottom of a list, however my current attempt just keeps overwriting one cell
as my current programming seems to have selected an actual cell reference. I
recorded the macro of me copying, then higlighting the cell at the top of the
listing column, hitting CTRL+DOWN CURSOR then down cursor again before
pasting. I thought this would leave the code to always drop to the bottom of
the list & then go one cell further, but this appears not to be the case!

Does anyone know a better (i.e. functioning!) way to program this?

Many thanks in advance, if you need any more info please reply.

cheers

jb
 
B

Bob Phillips

Activecell.copy activecell.offset(0,1).end(xldown)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

JB2010

Hi Bob


sounds good, but i cant work out where that will fit into the current script?


apologies, can you help again?

existing code looks like this:

Range("E1").Select
Selection.Copy
Range("B1").Select
Selection.End(xlDown).Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub


Any help gratefully received


cheers


jb
 
B

Bob Phillips

jb,

I would suggest that you don't need my code at all, just a small tweak to
your code to work with the activecell

Activecell.Copy
Range("B1").End(xlDown).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

You would change the B1 to wherever your list starts

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

Difficult to tell with the original code, but maybe this line:

Range("B1").End(xlDown).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

should be:

Range("B1").End(xlDown).offset(1,0).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Just to drop down one more row after that last cell.
 
Top