none
Copy every nth Cell RRS feed

  • Question

  • I am trying to copy every nth cell into a single column on another worksheet. For example, say I have a value in H1 and corresponding values every 96 cells. I would want to copy H2 + n96 where n is some integer until the end of my data set. I want to paste that into a column on a separate worksheet. Then after that, I want to start at cell H3 and copy the cells H3 + n96 and move that into a column in another worksheet.

    I've tried to solve this problem all day without any success. I thought it would be a good idea to create a loop that copies the sequence H2 + n96 and pastes it into another sheet. This is my ODgenerator sub. Then I thought that I could put an external loop around that and just increment my StartCopyRow and my StartPasteColumn and have it carry out the rest of the copy/paste sequences (H3 + n96, H4 + n96, etc) but only the first column appears...i need help 

    Sub Landon()

    Dim StartCopyRow As Integer
    Dim StartCopyColumn As Integer
    Dim StartPasteRow As Integer
    Dim StartPasteColumn As Integer

    StartCopyRow = 2
    StartCopyColumn = 8
    StartPasteRow = 2
    StartPasteColumn = 1

    Do

    Call ODGenerator(StartCopyRow, StartCopyColumn, StartPasteRow, StartPasteColumn)

    StartCopyRow = StartCopyRow + 1
    StartPasteColumn = StartPasteColumn + 1

    Loop Until StartCopyRow = 97

    End Sub

     

    Sub ODGenerator(CopyRow As Integer, CopyColumn As Integer, PasteRow As Integer, PasteColumn As Integer)
       
        Do
            Cells(CopyRow, CopyColumn).Copy Sheets("OD").Cells(PasteRow, PasteColumn)
            CopyRow = CopyRow + 96
            PasteRow = PasteRow + 1
           
        Loop Until IsEmpty(Cells(CopyRow, CopyColumn))

    End Sub

    Wednesday, June 13, 2012 4:09 AM

All replies

  • Hi mynamealreadyinuse,

    You need to have control over your variables. You can see only 1 column because your CopyRow is passed ByRef and in next column empty cell is copied from below your OD data. Try like this:

    Sub ODGenerator(ByVal CopyRow As Integer, ByVal CopyColumn As Integer, ByVal PasteRow As Integer, ByVal PasteColumn As Integer)

    By passing variables ByRef you are changing your StartCopyRow argument in here:
    StartCopyRow = StartCopyRow + 1
    and here:
    CopyRow = CopyRow + 96

    Have a look at Passing Variables By Reference And By Value.



    • Edited by Abcadlo Wednesday, June 13, 2012 7:09 AM
    • Proposed as answer by Abcadlo Tuesday, June 19, 2012 8:18 AM
    Wednesday, June 13, 2012 6:59 AM