VBA code to pull data from visible (unhidden) rows to sheet 2

S

SafetyGirlTanya

New Member
Joined
Apr 23, 2017
Messages
2
Hi there!

I am working on building a workbook where on Sheet1, a driver's log book data is entered.
The columns on Sheet1 that have data that needs to be referenced in a report on Sheet2 are :(Date) & (Violations)x22

The first 10 rows of Sheet1 are headers and then there are 370 rows of data on Sheet1. As the data gets entered (in 15 day batches), I would like the information to be referenced in Sheet2 so that a report can be printed. Once the report is printed, the rows on Sheet1 are then manually hidden and the data entry continues. There are 15 available rows in Sheet2 that I would like to be able to reference to Sheet1.

**IDEALLY** I would like to have rows 4-18 on Sheet2 reset and start pulling data from the next 15 visible (unhidden) rows on Sheet1.


I am using this array to link all of the violations in to one cell of the report on Sheet2:

=JOINXL(IF(NOT(ISBLANK(Sheet1!I12:AD12)),Sheet1!$I$3:$AD$3&", ",""),"")
These are then referenced to a group of merged cells for aesthetics

And this formula to count the violations if they are Non-Compliances
=IF(COUNTIF(Sheet1!I12:O12,"X")>0,"X","")

And this formula to count the violations if they are Non-Conformances

=IF(COUNTIF(Sheet1!P12:AB12,"X")>0,"X","")





Is this even possible? I am using Excel 2010


Thank you for any ideas or assistance!!

T.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Re: Need assistance for VBA code to pull data from visible (unhidden) rows to sheet 2

T.

Welcome to the Board.

Are you open to a vba approach? If so, can you post sample data please? As well as a sample report?

Thanks,

tonyyy
 
Upvote 0
Re: Need assistance for VBA code to pull data from visible (unhidden) rows to sheet 2

[TABLE="width: 401"]
<tbody>[TR]
[TD="class: xl86, width: 72, bgcolor: silver"]date
[/TD]
[TD="class: xl82, width: 47, bgcolor: silver"]hrs
[/TD]
[TD="class: xl87, width: 50, bgcolor: silver"]on duty
[/TD]
[TD="class: xl88, width: 47, bgcolor: silver"]reset
[/TD]
[TD="class: xl71, width: 32, bgcolor: silver"][/TD]
[TD="class: xl72, width: 33, bgcolor: silver"][/TD]
[TD="class: xl72, width: 33, bgcolor: silver"][/TD]
[TD="class: xl89, width: 33, bgcolor: silver"][/TD]
[TD="class: xl84, width: 18, bgcolor: silver, align: right"]a
[/TD]
[TD="class: xl85, width: 18, bgcolor: silver, align: right"]b
[/TD]
[TD="class: xl85, width: 18, bgcolor: silver, align: right"]c
[/TD]
[TD="class: xl85, width: 18, bgcolor: silver, align: right"]d
[/TD]
[TD="class: xl85, width: 18, bgcolor: silver, align: right"]e
[/TD]
[TD="class: xl85, width: 18, bgcolor: silver, align: right"]f
[/TD]
[TD="class: xl90, width: 18, bgcolor: silver"]g
[/TD]
[TD="class: xl91, width: 18, bgcolor: silver, align: right"]h
[/TD]
[TD="class: xl83, width: 18, bgcolor: silver, align: right"]i
[/TD]
[TD="class: xl83, width: 18, bgcolor: silver, align: right"]j
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]31-Dec-16
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12.00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]0.00
[/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl76, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl77, bgcolor: transparent"][/TD]
[TD="class: xl78, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"]x
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1-Jan-17
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12.00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]24.00
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"]x
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2-Jan-17
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10.00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]34.00
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"]x
[/TD]
[TD="class: xl80, bgcolor: transparent"]x
[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3-Jan-17
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]16.00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50.00
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4-Jan-17
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]14.00
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]64.00
[/TD]
[TD="class: xl69, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"][/TD]
[TD="class: xl80, bgcolor: transparent"]x
[/TD]
[TD="class: xl81, bgcolor: transparent"][/TD]
[TD="class: xl73, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 1

[TABLE="width: 535"]
<tbody>[TR]
[TD="class: xl77, width: 480, bgcolor: #D9D9D9, colspan: 6"][/TD]
[TD="class: xl68, width: 127, bgcolor: #D9D9D9, colspan: 2"]Non Compliance
[/TD]
[TD="class: xl70, width: 103, bgcolor: #D9D9D9, colspan: 2"]Non Conformance
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]31-Dec-16
[/TD]
[TD="class: xl74, width: 400, bgcolor: transparent, colspan: 5"]Date,
[/TD]
[TD="class: xl72, bgcolor: transparent, colspan: 2"][/TD]
[TD="class: xl73, bgcolor: transparent, colspan: 2"]X
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1-Jan-17
[/TD]
[TD="class: xl80, width: 400, bgcolor: transparent, colspan: 5"]Date,
[/TD]
[TD="class: xl81, bgcolor: transparent, colspan: 2"][/TD]
[TD="class: xl82, bgcolor: transparent, colspan: 2"]X
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]2-Jan-17
[/TD]
[TD="class: xl80, width: 400, bgcolor: transparent, colspan: 5"]Less than 10 Hours Off Duty/Day, Less than 8 consecutive hours off-Duty,
[/TD]
[TD="class: xl81, bgcolor: transparent, colspan: 2"]X
[/TD]
[TD="class: xl82, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]3-Jan-17
[/TD]
[TD="class: xl80, width: 400, bgcolor: transparent, colspan: 5"]Truck Unit/Plate#,
[/TD]
[TD="class: xl81, bgcolor: transparent, colspan: 2"][/TD]
[TD="class: xl82, bgcolor: transparent, colspan: 2"]X
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]4-Jan-17
[/TD]
[TD="class: xl80, width: 400, bgcolor: transparent, colspan: 5"]Less than 8 consecutive hours off-Duty,
[/TD]
[TD="class: xl81, bgcolor: transparent, colspan: 2"]X
[/TD]
[TD="class: xl82, bgcolor: transparent, colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2


I did find this formula to get the dates to change, but like with my other formulas it will not change when the rows on sheet one are hidden:
=INDEX(Sheet1!A13:Sheet1!A402,MIN(IF(SUBTOTAL(3,OFFSET(Sheet1!A13,ROW(Sheet1!A13:Sheet1!A402)-ROW(Sheet1!A13),0)),ROW(Sheet1!A13:Sheet1!A402)-ROW(Sheet1!A13)+1)))

(Links the data in columns a:j in to one cell on sheet 2)
=JOINXL(IF(NOT(ISBLANK(Sheet1!I16:AD16)),Sheet1!$I$3:$AD$3&", ",""),"")


What I would like is when the rows containing date 31-dec16 to 4-jan-17 are hidden on sheet 1, the next rows 5-jan-17 to 9-jan-17 are shown on sheet2.

Hopefully this is a enough information for you, let me know if it is not!

I am open to a vba option as I can copy and paste into modules

Thanks
 
Upvote 0
Re: Need assistance for VBA code to pull data from visible (unhidden) rows to sheet 2

So, I'm having trouble understanding how you get from Sheet1 to Sheet2. :-(

The following, though, will at least populate the first 5 visible dates from Sheet1 to Sheet2...

Code: 
Sub GetDates()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow1 As Long, r As Long, i As Long '"r" represents rows on Sheet1, "i" represents rows on Sheet2
    
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
    For r = 2 To LastRow1
        If Rows(r).Hidden <> True Then
            With ws2
                .Range(.Cells(2, 1), .Cells(6, 1)).Value = ws1.Range(ws1.Cells(r, 1), ws1.Cells(r + 4, 1)).Value
'                For i = 2 To 6
'                    .Cells(i,3).formula="=IF(COUNTIF(Sheet1!I12:O12,"X")>0,"X","")"
'                    .Cells(i,4).formula="=IF(COUNTIF(Sheet1!P12:AB12,"X")>0,"X","")"
'                Next i
                Exit For
            End With
        End If
    Next r
    MsgBox "Done!"
End Sub

The 4 lines that are commented out provide the structure for populating Sheet2.Range("C2:D6") with the NonCompliance and NonConformance formulas. The formulas will need to be adapted to include the "r" variable.

I'll be taking a long weekend and won't return until Tuesday. If someone else doesn't pick up I'll look at it again then.

Cheers,

tonyyy
 
Upvote 0
Re: Need assistance for VBA code to pull data from visible (unhidden) rows to sheet 2

The following contains the adapted formulas, but again, not sure if this is what you're looking for...

Code: 
Sub GetDates()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow1 As Long, r As Long, i As Long '"r" represents rows on Sheet1, "i" represents rows on Sheet2
    
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
    For r = 2 To LastRow1
        If ws1.Rows(r).Hidden <> True Then
            With ws2
                .Range(.Cells(2, 1), .Cells(6, 1)).Value = ws1.Range(ws1.Cells(r, 1), ws1.Cells(r + 4, 1)).Value
                For i = 2 To 6
                    .Cells(i, 3).Formula = "=IF(COUNTIF(Sheet1!I" & r + i - 2 & ":O" & r + i - 2 & "," & """X"")>0,""X"","""")"
                    .Cells(i, 4).Formula = "=IF(COUNTIF(Sheet1!P" & r + i - 2 & ":AB" & r + i - 2 & "," & """X"")>0,""X"","""")"
                Next i
                Exit For
            End With
        End If
    Next r
    MsgBox "Done!"
End Sub
 
Upvote 0
Re: Need assistance for VBA code to pull data from visible (unhidden) rows to sheet 2

And the JOINXL formula added to Sheet2 Column2...

Code: 
Sub GetDates()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim LastRow1 As Long, r As Long, i As Long '"r" represents rows on Sheet1, "i" represents rows on Sheet2
    
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    LastRow1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
    For r = 2 To LastRow1
        If ws1.Rows(r).Hidden <> True Then
            With ws2
                .Range(.Cells(2, 1), .Cells(6, 1)).Value = ws1.Range(ws1.Cells(r, 1), ws1.Cells(r + 4, 1)).Value
                For i = 2 To 6
                    .Cells(i, 2).Formula = "=JOINXL(IF(NOT(ISBLANK(Sheet1!I" & r + i - 2 & ":AD" & r + i - 2 & ")),Sheet1!$I$3:$AD$3&"", "",""""),"""")"
                    .Cells(i, 3).Formula = "=IF(COUNTIF(Sheet1!I" & r + i - 2 & ":O" & r + i - 2 & "," & """X"")>0,""X"","""")"
                    .Cells(i, 4).Formula = "=IF(COUNTIF(Sheet1!P" & r + i - 2 & ":AB" & r + i - 2 & "," & """X"")>0,""X"","""")"
                Next i
                Exit For
            End With
        End If
    Next r
    MsgBox "Done!"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
428
mumps
mumps
M
  • Question Question
VBA that pulls data based on multiple criteria
Replies
0
Views
322
Mouse7681
M
W
  • Solved
VBA - Match two values from Table1 to two in Table2 and move/align
2
Replies
15
Views
377
Welshy1491
W
F
  • Solved
Vba code to pull data from multiple tabs to master
Replies
3
Views
636
rlv01
R
F
  • Solved
VBA Macro - Automatic Update "Master" data sheet from multiple sheet
Replies
7
Views
1K
Trebor76
T

Forum statistics

Threads
1,224,828
Messages
6,181,212
Members
453,023
Latest member
alabaz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top