none
How to Extract certain rows and repeating the process in VBA Excel RRS feed

  • Question

  • Hey Guys,

    I want to extract certain rows from an Excel file and place them on one sheet, and repeat the process hundreds of times, so that I have hundreds of sheet each containing pertinent rows from the source Excel file.

    This is what I have been able to come up with:

    sub Extract ()

    Set range2 = Range("A8:F20, A21:F31, A32:F47, A48:F60, A61:F71, A72:F87") range2.Copy Set NewSheet = Worksheets.Add ActiveCell.PasteSpecial Paste:=xlPasteValues For I = 1 To 20 'to repeat the extracted data range2.Copy Set NewSheet = Worksheets.Add ActiveCell.PasteSpecial Paste:=xlPasteValues Next I End Sub

     Output: My range combined the extracted files into a sheet which is the repeated. But I would like to extract each range into a new sheets (i.e 6 sheets in total) which can then be repeated severally


    • Moved by Bill_Stewart Thursday, October 12, 2017 2:09 PM Move to more appropriate forum
    Thursday, October 12, 2017 1:47 AM

All replies

  • This is not an Excel VBA forum.  You need to post VBA questions to the Excel VBA forum.


    \_(ツ)_/

    Thursday, October 12, 2017 1:56 AM
  • Sorry this is my first time. don't know my way around
    Thursday, October 12, 2017 2:51 AM
  • Hi Jibz,

    You could put these range into an array and then iterate through the array.

    Here is the example.

    Sub Extract()
    
    arrlist = Array(Range("A1:A10"), Range("B1:B10"), Range("C1:C10"), Range("D1:D10"), Range("E1:E10"), Range("F1:F10"))
    
        For i = LBound(arrlist) To UBound(arrlist)
    
        arrlist(i).Copy
    
        Set NewSheet = Worksheets.Add
    
        ActiveCell.PasteSpecial Paste:=xlPasteValues
    
        Next i
    
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Terry Xu - MSFT Wednesday, November 1, 2017 2:47 AM
    Friday, October 13, 2017 9:16 AM