Copy/Paste Filtered Table x Number of Times

[wdp74]

Hello all!

I have a table on Sheet2 that is filtered to show certain data. I need to copy all the shown data (rows C:E) to Sheet3 (cell A5) and has to be copied x number of time based on value from Sheet1 (cell B4). For example, if the Sheet1 Cell B4 has "10", the shown filtered data will be copied from Sheet 2 and Pasted sheet 3, 10 times.

Any help will be appreciated!

 

[wdp74]

Apologies, I mean it has to be pasted x number of times....

 

[JoeMo]

Does your Table have headers? If yes, do you want the headers included in the pastes or just the filtered data body range?
Do you want any empty rows between pastes if x>1?

 

[JoeMo]

This will paste the data body range x times as values with no empty rows between pastes.

Sub PasteFilteredTable()
Dim LO As ListObject, NumTimes As Long, NxRw As Long, Ctr As Long
Application.ScreenUpdating = False
Set LO = Sheets("Sheet2").ListObjects("Table1")
NumTimes = Sheets("Sheet1").Range("B4").Value
NxRw = 5
If NumTimes > 0 Then
    LO.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
    Do
    With Sheets("Sheet3")
        .Cells(NxRw, "A").PasteSpecial Paste:=xlValues
        Ctr = Ctr + 1
        NxRw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    End With
    Loop While Ctr < NumTimes
End If
Application.ScreenUpdating = True
End Sub

 

[wdp74]

Does your Table have headers? If yes, do you want the headers included in the pastes or just the filtered data body range?
Do you want any empty rows between pastes if x>1?

Hi Joe Moe! The table had headers but I do not want them copied/pasted.

Wow! This works great. Only thing, what if I only needed 3 columns (Headers for columns C,D,E - Low, Mid, High).

 

[wdp74]

Figured it out but couldn't have done it without your help. Thank you!

 

[JoeMo]

Figured it out but couldn't have done it without your help. Thank you!

You are welcome - thanks for the reply.