Creating 5 reports from top 5 rows of filtered table

Super Dave

I'm new to coding macros. I've been pulling a lot from this site to get me up to speed and it's helped a ton.

I'm trying to create one report for each row of data for the top five rows of visible data in a filtered table. I've seen several kind of close examples listed, but I haven't figured out how to integrate them into a functioning product. Help getting me over the final hurdle would be much appreciated.

My table has a header row from A2:T2, so I need to pull from the five rows below the header using the xlCellTypeVisible so only the visible rows are selected. The length of the table varies daily but it's never shorter than 150 rows.

Here's the data code I'm trying to create a new sheet, and pull from the first row of the table:

' Create new sheet for report
Sheets.Add After:=Sheets(Sheets.Count) 

' Add Part number, Description & Company to header

' Part Number
Sheets("Variance Data").Range("K3").Copy Destination:=ActiveSheet.Range("A2")
Range("A2").Select
Selection.Font.Bold = True
' Part description
Sheets("Variance Data").Range("L3").Copy Destination:=ActiveSheet.Range("A3")
' Customer
Sheets("Variance Data").Range("G3").Copy Destination:=ActiveSheet.Range("F3")

' Add info from Variance Data tab

' Work Center
Sheets("Variance Data").Range("C3").Copy Destination:=ActiveSheet.Range("A6")
' Work Order
Sheets("Variance Data").Range("H3").Copy Destination:=ActiveSheet.Range("C6")
' Task
Sheets("Variance Data").Range("D3").Copy Destination:=ActiveSheet.Range("D6")
' Seq #
Sheets("Variance Data").Range("I3").Copy Destination:=ActiveSheet.Range("E6")
' Qty
Sheets("Variance Data").Range("M3").Copy Destination:=ActiveSheet.Range("F6")

' Est Hrs
Sheets("Variance Data").Range("O3").Copy Destination:=ActiveSheet.Range("B8")
' Act. Hrs
Sheets("Variance Data").Range("Q3").Copy Destination:=ActiveSheet.Range("B9")
' Var. Hrs
Sheets("Variance Data").Range("S3").Copy Destination:=ActiveSheet.Range("B10")
' Est Cost
Sheets("Variance Data").Range("P3").Copy Destination:=ActiveSheet.Range("E8")
' Act. Cost
Sheets("Variance Data").Range("R3").Copy Destination:=ActiveSheet.Range("E9")
' Var. Cost
Sheets("Variance Data").Range("T3").Copy Destination:=ActiveSheet.Range("E10")

ActiveSheet.Name = Range("A2").Value

How do I change this so I loop this to create five sheets, one for each row of data from the main data sheet titled "Variance Data" and fill in to the new sheet?

Thanks for your help!

Ryszard Jędraszyk

If you need any adjustments let me know. Remember that if you want to run the macro twice, you need to remove newly created sheets, because trying to create sheets with the same name will cause an error.

The below code works if assigned for freshly created ActiveX command button (gets a default name CommandButton1), no matter which of your sheets you have the button in.

Option Explicit
Option Base 1

Private Sub CommandButton1_Click()

Dim v_data As Variant
Dim mainsheet As String
Dim thelastrow As Long, visibleRowsCount As Long, arrayRow As Long
Dim ws As Worksheet

'disable screen updating on code execution for faster performance and no screen flickering
Application.ScreenUpdating = False
'name of your data sheet
mainsheet = "Variance Data"

'create an array from sheet data, starting range is defined in code, last row is the last row containing data in your sheet
'only visible rows are taken
With ThisWorkbook.Worksheets(mainsheet)
    thelastrow = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    v_data = .Range("a3:t" & thelastrow)

'works for 5 top visible rows - create sheets and populate them with data from array
Do Until visibleRowsCount = 5 Or arrayRow = UBound(v_data)
    arrayRow = arrayRow + 1
    If Not Rows(arrayRow + 2).Hidden Then
        visibleRowsCount = visibleRowsCount + 1
        Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
        ws.Name = "Report" & visibleRowsCount
        With ThisWorkbook.Sheets("Report" & visibleRowsCount)
            'Cell value of your new report worksheet = value from data array
            .Range("a5").Value = v_data(arrayRow, 5) 'take data from 5th column of the current row
            .Range("b3").Value = v_data(arrayRow, 1) 'take data from 1st column of the current row
            .Range("d3").Value = v_data(arrayRow, 1) 'take the same data from 1st column of the current row and put in a different cell
        End With
    End If
Loop

End With

Application.ScreenUpdating = True

End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Selecting the top 5 rows from a joined table, into the result of a larger query?

From Dev

Selecting the top 5 rows from a joined table, into the result of a larger query?

From Dev

AngularJs Table with showing top 5 and bottom 5 rows?

From Dev

Top 5 rows by month

From Dev

how to select top 5 products from table

From Dev

Get top 5 rows per row in related table

From Dev

Select TOP row in SQL and get 5 rows of end table

From Dev

Excel VBA: Filter and copy from top 5 rows/cells

From Dev

How to retrieve sum of 5 top score from a table in mysql

From Dev

SQLITE - Get Top 5 paid customers from Bill Table

From Dev

PostgreSQL: Obtain rows from table from 5 minutes ago

From Dev

Create a new regular table from visible and filtered rows of DataTable

From Dev

Create a new regular table from visible and filtered rows of DataTable

From Dev

How to select top 5 after 20 rows

From Dev

How to select top 5 after 20 rows

From Dev

How to retrieve top 5 values from each column of each of the table of same database and then get over all top 5 in the whole database

From Dev

ui5: move selected rows from table1 to table2 in another view

From Dev

load fetched and filtered json from htm5 localstorage

From Dev

Ignore tfoot table rows when creating Highcharts from HTML table

From Dev

Force table to display 5 rows with or without data

From Dev

Top 5 scores from google leaderboard

From Dev

How to pick top 5 values from a hash?

From Dev

A Top Bar with 2 navigation rows - Zurb Foundation 5

From Dev

LINQ grouping and ordering of top 5 rows into ViewModel List

From Dev

How to select top (5) rows with specified Id and latest date?

From Dev

Count Top 5 Elements spread over rows and columns

From Dev

in R output shows top and bottom 5 rows of data

From Dev

Python - Finding the top 5 rows containing a word in a dataframe

From Dev

MySQL: Select top 5 rows based on ID and find Subtotal

Related Related

  1. 1

    Selecting the top 5 rows from a joined table, into the result of a larger query?

  2. 2

    Selecting the top 5 rows from a joined table, into the result of a larger query?

  3. 3

    AngularJs Table with showing top 5 and bottom 5 rows?

  4. 4

    Top 5 rows by month

  5. 5

    how to select top 5 products from table

  6. 6

    Get top 5 rows per row in related table

  7. 7

    Select TOP row in SQL and get 5 rows of end table

  8. 8

    Excel VBA: Filter and copy from top 5 rows/cells

  9. 9

    How to retrieve sum of 5 top score from a table in mysql

  10. 10

    SQLITE - Get Top 5 paid customers from Bill Table

  11. 11

    PostgreSQL: Obtain rows from table from 5 minutes ago

  12. 12

    Create a new regular table from visible and filtered rows of DataTable

  13. 13

    Create a new regular table from visible and filtered rows of DataTable

  14. 14

    How to select top 5 after 20 rows

  15. 15

    How to select top 5 after 20 rows

  16. 16

    How to retrieve top 5 values from each column of each of the table of same database and then get over all top 5 in the whole database

  17. 17

    ui5: move selected rows from table1 to table2 in another view

  18. 18

    load fetched and filtered json from htm5 localstorage

  19. 19

    Ignore tfoot table rows when creating Highcharts from HTML table

  20. 20

    Force table to display 5 rows with or without data

  21. 21

    Top 5 scores from google leaderboard

  22. 22

    How to pick top 5 values from a hash?

  23. 23

    A Top Bar with 2 navigation rows - Zurb Foundation 5

  24. 24

    LINQ grouping and ordering of top 5 rows into ViewModel List

  25. 25

    How to select top (5) rows with specified Id and latest date?

  26. 26

    Count Top 5 Elements spread over rows and columns

  27. 27

    in R output shows top and bottom 5 rows of data

  28. 28

    Python - Finding the top 5 rows containing a word in a dataframe

  29. 29

    MySQL: Select top 5 rows based on ID and find Subtotal

HotTag

Archive