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

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
<(E-Mail Removed)> 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

hi
try this.....

x.resize(5, 1).copy

read up on "resize range" in vb help.

Regards
FSt1

"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

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?
--
Copy & paste developer


"FSt1" wrote:

> hi
> try this.....
>
> x.resize(5, 1).copy
>
> read up on "resize range" in vb help.
>
> Regards
> FSt1
>
> "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

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
> <(E-Mail Removed)> 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
> .
>

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" wrote:

> 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?
> --
> Copy & paste developer
>
>
> "FSt1" wrote:
>
> > hi
> > try this.....
> >
> > x.resize(5, 1).copy
> >
> > read up on "resize range" in vb help.
> >
> > Regards
> > FSt1
> >
> > "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

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.
--
Copy & paste developer


"FSt1" wrote:

> 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" wrote:
>
> > 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?
> > --
> > Copy & paste developer
> >
> >
> > "FSt1" wrote:
> >
> > > hi
> > > try this.....
> > >
> > > x.resize(5, 1).copy
> > >
> > > read up on "resize range" in vb help.
> > >
> > > Regards
> > > FSt1
> > >
> > > "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