Need code for Excel Simple Visual Basic Macro to select next avai.

[Guest]

I am trying to write this simple macro but I cannot figure how HOW to select
the next available row of a database of addresses.
Below is the macro that I have now it works BUT it always select the A8 Cell,

Rows("1:1").Select
Selection.Copy
Selection.End(xlDown).Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Instead I would like it to do the following:
1) Rows("1:1").Select [This is OK]
2) Selection.Copy [This is OK]
3) Selection.End(xlDown).Select [This is OK]
Here >>> I need a command to simply MOVE DOWN ONE ROW.
and then
4) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks :=False, Transpose:=False [This is OK as well]

In other words is just like when in a spread sheet of say 10 Rows, I
manually press:
END, DOWN, (will take me to the las occupied cell), + DOWN (will take me to
the next available cell.

Can anyone help me PLEASE.
Thanks

 

[Bob Phillips]

Marco,

Is this what you mean?

Rows("1:1").Select
Selection.Copy
Selection.End(xlDown).Offset(1,0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

--

HTH

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

 

[RB Smissaert]

This single line will do this:

Rows(1).Copy Cells(1).End(xlDown).Offset(1, 0)


RBS

 

[Guest]

activecell.offset(1,0).select

 

[Don Guillett]

Sub copyrow()
'to use the next avaible row
x = Cells(1, 1).End(xlDown).Row + 1

'to use the last available row - usually used.
'x = Cells(rows.count, 1).End(xlup).Row + 1

'line below to copy
'Rows(1).Copy rows(x)

'line below to copy values only
Rows(x).Value = Rows(1)

End Sub

 

[Guest]

THANK YOU! It works perfectly...
You must be laughing abou my ignorance on this, but I am a beginner and a
REALLY appreciated you fast and correct reply!
THANKS!!!

 

[Don Guillett]

Which one did you like best.

--
Don Guillett
SalesAid Software
(e-mail address removed)

THANK YOU! It works perfectly...
You must be laughing abou my ignorance on this, but I am a beginner and a
REALLY appreciated you fast and correct reply!
THANKS!!!

I am trying to write this simple macro but I cannot figure how HOW to select
the next available row of a database of addresses.
Below is the macro that I have now it works BUT it always select the A8 Cell,

Rows("1:1").Select
Selection.Copy
Selection.End(xlDown).Select
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Instead I would like it to do the following:
1) Rows("1:1").Select [This is OK]
2) Selection.Copy [This is OK]
3) Selection.End(xlDown).Select [This is OK]
Here >>> I need a command to simply MOVE DOWN ONE ROW.
and then
4) Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks :=False, Transpose:=False [This is OK as well]

In other words is just like when in a spread sheet of say 10 Rows, I
manually press:
END, DOWN, (will take me to the las occupied cell), + DOWN (will take me to
the next available cell.

Can anyone help me PLEASE.
Thanks