ExcelBanter - Increasing Row Value

I don't follow exactly what you want to do, eg

(i.e. A240:G240 to A241 to:G240) ?

but in essence you want to increment a counter, here are two different
approaches. Use the name method if you want the row to increment down
between sessions

Sub test1()
Dim rng As Range
Static nRow As Long

If nRow = 0 Then
nRow = 240
Else
nRow = 240 + 1
End If

With ActiveSheet
Set rng = .Range(.Cells(nRow, 1), .Cells(nRow, 6))
End With

MsgBox rng.Address

End Sub

Sub test2()
Dim rng As Range

On Error Resume Next
Set rng = Range("aName")
On Error GoTo 0
If rng Is Nothing Then
Set rng = ActiveSheet.Range("A240:G240")
ActiveWorkbook.Names.Add "aName", rng
' optional
' ActiveWorkbook.Names("aName").Visible = False
End If
MsgBox rng.Address
ActiveWorkbook.Names.Add "aName", rng.Offset(1)

End Sub

BTW, no need to select anything in your code

Regards,
Peter T

wrote in message
...
Hi there,
Just wondering if anyone could help me with the following problem:

Range("A240:G240").Select
Selection.Copy
Range("A241").Select
ActiveSheet.Paste
Range("A240:G240").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A242").Select

Each time I run the macro, I want the row value to be increasing (i.e.
A240:G240 to A241 to:G240). Range A240-G240 refers to the value from
another sheet which has to be copied to the following row and then
paste the value to A240-G240 (no formulas).

If anyone could help, that would be really appreciated.
Thanks
AN