copy a range from active cell

[Axel]

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

 

[Chip Pearson]

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

 

[FSt1]

hi
try this.....

x.resize(5, 1).copy

read up on "resize range" in vb help.

Regards
FSt1

 

[Axel]

Thank you FSt1, that worked perfekt.

I also struggle with this:
ActiveSheet.Paste link:=True, Transpose:=True
When i record a macro and select "transpose checkbox", the "Paste Link"
button become inaccessible in the "Paste Special" dialogbox.

Anyone who now a different way to do this?

 

[Axel]

Thanks Chip. Your solutions are always helpful.

 

[FSt1]

hi
sorry for the delay getting back. i got hung up.
could you explain more about what your are attempting to do with the code?

Regards
FSt1

 

[Axel]

I changed the philosophy in the workbook and use past value insted of paste
link.
Then the "Transpose" command work just fine.

Selection.PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

Thank's for your response. you made my day at the first question.