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