Splitting up an excel file to multiple tabs by "visible" rows.

[BasicUserWithExp]

I found a post: "Splitting up an excel file by rows." it works well for what i need, but I want it to split the rows on an already filtered sheet.

I have a master list of parts. the list contains a column for qty, I filter it to show only parts with quantity greater than zero. That list then needs to only show a max of 60 items per sheet.
here is the code:

Sub CopyTable()
 
    'Set dimensions
    Dim Table As Range, TableArray(), _
        CutValue As Integer, Cntr As Integer, _
        TempArray(), Width As Integer, _
        x As Integer, y As Integer, _
        Height As Long, Rep As Integer, _
        LoopReps As Long
 
    'Get data
    Set Table = Application.InputBox("Specify range to copy", _
        Default:=ActiveCell.CurrentRegion.Address, Type:=8)
    CutValue = InputBox("How many rows should the chunks be?", _
        Default:=900)
    Width = Table.Columns.Count
    Height = Table.Rows.Count
 
    'Write to array
    TableArray = Table
    ReDim TempArray(1 To CutValue, 1 To Width)
    Rep = Application.WorksheetFunction.RoundUp(Height / CutValue, 0)
    LoopReps = CutValue
 
    'Loop through all new sheets
    For Cntr = 0 To Rep - 1
        If Height - Cntr * CutValue < CutValue Then _
            LoopReps = Height - Cntr * CutValue
 
        For x = 1 To Width
            For y = 1 To LoopReps
                TempArray(y, x) = TableArray(y + Cntr * CutValue, x)
            Next y
        Next x
 
        Worksheets.Add
        Range("A1").Resize(LoopReps, Width) = TempArray
    Next Cntr
End Sub

 

[Fuji]

I think you will need to do it by your hand.

 

[BasicUserWithExp]

I found a way to use power query to create a table with filters. it works but i am unsure how to use the filter to count the rows, so only 60 are per page. Dropbox