none
Copying multiple rows in multiple worksheets and pasting in a table to new worksheet RRS feed

  • Question

  • Hello! I am trying to copy multiple rows that have data in them that are not consecutive for only unhidden worksheets to a table in the same workbook. The information in each will possibly be different each time but the rows and columns that the information is being copied from will always be the same. I included the row numbers and columns letters. Can someone help me with this VBA script? The following is an example of the information in each worksheet and if there is data it will be in the same row and column but will only go down 50 rows each time for 9 times or 9 profiles and starting on row 9 but not every 50th row will have data. Thanks!

    Copy Sheet 1
    A B C D E F G H  I  J K
    Row
    9 - apples apples apples apples apples 2 2 2 2 2
    59 - bananas bananas bananas bananas bananas 1 1 1 1 1
    109 - grapes grapes grapes grapes grapes 1 1 1 1 1
    Copy Sheet 2
    A B C D E F G H  I  J K
    9 - oranges oranges oranges oranges oranges 2 2 2 2 2
    59 - bananas bananas bananas bananas bananas 1 1 1 1 1
    109 - cherries cherries cherries cherries cherries 1 1 1 1 1

    Output

    Sheet 3

    A B C D E F G H  I  J K
    1 - apples apples apples apples apples 2 2 2 2 2
    2 - bananas bananas bananas bananas bananas 2 2 2 2 2
    3 - grapes grapes grapes grapes grapes 1 1 1 1 1
    4 - oranges oranges oranges oranges oranges 2 2 2 2 2
    5 - bananas bananas bananas bananas bananas 2 2 2 2 2
    6 - cherries cherries cherries cherries cherries 1 1 1 1 1


    Friday, November 6, 2015 11:11 PM

Answers

  • >>> The information in each will possibly be different each time but the rows and columns that the information is being copied from will always be the same. I included the row numbers and columns letters. Can someone help me with this VBA script?

    According to your description, you could refer to below code that programmatically select every nth row in a range then copy the range to the specified range in Excel:

    Sub CopyEveryNthRow()
    
        ' Initialize ColsSelection equal to the number of columns in the
    
        ' selection.
    
        ColsSelection = Selection.Columns.Count
    
        ' Initialize RowsSelection equal to the number of rows in your
    
        ' selection.
    
        RowsSelection = Selection.Rows.Count
    
        ' Initialize RowsBetween equal to three.
    
        RowsBetween = 3
    
        ' Initialize Diff equal to one row less than the first row number of
    
        ' the selection.
    
        Diff = Selection.Row - 1
    
        ' Resize the selection to be 1 column wide and the same number of
    
        ' rows long as the initial selection.
    
        Selection.Resize(RowsSelection, 1).Select
    
        ' Resize the selection to be every third row and the same number of
    
        ' columns wide as the original selection.
    
        Set FinalRange = Selection. _
    
           Offset(RowsBetween - 1, 0).Resize(1,ColsSelection)
    
        ' Loop through each cell in the selection.
    
        For Each xCell In Selection
    
            ' If the row number is a multiple of 3, then . . .
    
            If xCell.Row Mod RowsBetween = Diff Then
    
                ' ...reset FinalRange to include the union of the current
    
                ' FinalRange and the same number of columns.
    
                Set FinalRange = Application.Union _
    
                    (FinalRange, xCell.Resize(1,ColsSelection))
    
            ' End check.
    
            End If
    
        ' Iterate loop.
    
        Next xCell
    
        ' Select the requested cells in the range.
    
        FinalRange.Select
         
         'copy the range to the specified range or to the Clipboard
         Worksheets("Sheet1").Range("A1:D4").Copy _ 
    
        destination:=Worksheets("Sheet2").Range("E5")
    
    End Sub
    

    Note: If you want to select every 50th row, set RowsBetween to 50.

    For more information, click here to refer about How to programmatically select every nth row in a range in Excel

    and here to refer about Range.Copy Method (Excel)


    Tuesday, November 10, 2015 8:24 AM