Thanks Chip. Your solutions are always helpful.
--
Copy & paste developer


"Chip Pearson" wrote:

You can use the Resize method to expand a range to the size you want.
E.g.,

ActiveCell.Resize(5, 1).Copy

This resizes the range of the active cell to 5 rows and 1 column.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com





On Thu, 8 Apr 2010 06:09:01 -0700, Axel
wrote:

Hi!
I try to make the code so it copy the active cell and the next five cells
below.
The active cell copy works fine, but am not able to find a way to copy the
hole range.

The macro to loop through cells with contents:
-----------------------------------------------------
Private Sub CommandButton1_Click()
Sheets("Sheet2").Select

Dim x As Range


For Each x In Range("A5:M5")
If Not IsEmpty(x) Then
x.Copy

Sheets("Sheet1").Select
Run "kopiering"
End If

Next
End Sub
-------------------------------------------------------------
The macro that past the link value to the next sheet.

Sub kopiering()
Dim rngDestination As Range

Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
rngDestination.Select

ActiveSheet.Paste link:=True

----------------------------------
Thanks in advance
Copy & paste developer
.