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

[SafetyGirlTanya]

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.

 

[tonyyy]

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

 

[SafetyGirlTanya]

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

date

hrs

on duty

reset

a

b

c

d

e

f

g

h

i

j

31-Dec-16

12.00

0.00

x

1-Jan-17

12.00

24.00

x

2-Jan-17

10.00

34.00

x

x

3-Jan-17

16.00

50.00

4-Jan-17

14.00

64.00

x

<tbody>
</tbody>

Sheet 1

	Non Compliance	Non Conformance
31-Dec-16	Date,		X
1-Jan-17	Date,		X
2-Jan-17	Less than 10 Hours Off Duty/Day, Less than 8 consecutive hours off-Duty,	X
3-Jan-17	Truck Unit/Plate#,		X
4-Jan-17	Less than 8 consecutive hours off-Duty,	X

<tbody>
</tbody>

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

 

[tonyyy]

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...

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

 

[tonyyy]

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...

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

 

[tonyyy]

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

And the JOINXL formula added to Sheet2 Column2...

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