Copy Text

**aftabn10** · 11-12-2008, 07:24 AM

Hi, i am looking to write a macro to copy text from every 3rd row in my worksheet and then paste this elsewhere. How is this possible?

Thanks in advance.

**royUK** · 11-12-2008, 07:39 AM

Something like this

Dim R      As Long
    Dim RwCnt  As Long
    Dim rCopy  As Range
    With ActiveSheet
        RwCnt = .UsedRange.Rows.Count
        For R = RwCnt To 1 Step -3
            If rCopy Is Nothing Then
                Set rCopy = .Cells(R, 1).EntireRow
            Else: Set rCopy = Union(rCopy, .Cells(R, 1).EntireRow)
            End If
        Next R
        rCopy.Copy Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With

**aftabn10** · 11-12-2008, 08:04 AM

Thanks royUK, ill give that a go.

**aftabn10** · 11-12-2008, 08:09 AM

royuk, I get a run-time error '1004' on the following line:

rCopy.Copy Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Any ideas why?

**royUK** · 11-12-2008, 08:12 AM

Please use Code Tags.

Have you got a sheet2?

**aftabn10** · 11-12-2008, 08:29 AM

Sorry royuk. I do have Sheet2 and Sheet3 so thats why i dont understand why it comes up with the error. Any ideas?

Thanks once again.

**royUK** · 11-12-2008, 08:36 AM

No, can you attach the workbook

**aftabn10** · 11-12-2008, 08:44 AM

royuk, for some odd reason it comes up with the error, but it has now copied the text over. Thanks for all your help!

**royUK** · 11-12-2008, 08:48 AM

It shouldn't give any error. Try this

  Dim R      As Long
    Dim RwCnt  As Long
    Dim rCopy  As Range
    With ActiveSheet
        RwCnt = .UsedRange.Rows.Count
        For R = RwCnt To 1 Step -3
            If rCopy Is Nothing Then
                Set rCopy = .Cells(R, 1).EntireRow
            Else: Set rCopy = Union(rCopy, .Cells(R, 1).EntireRow)
            End If
        Next R
        With Sheet2
            rCopy.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
        End With
        End With

Copy Text

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy Text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions