Answered by:
Copying multiple rows in multiple worksheets and pasting in a table to new worksheet
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
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)
 Edited by David_JunFeng Tuesday, November 10, 2015 8:25 AM
 Proposed as answer by David_JunFeng Tuesday, November 17, 2015 2:32 PM
 Marked as answer by Fei XueMicrosoft employee, Moderator Wednesday, November 18, 2015 9:44 AM