Loop through worksheet to extract data while row not blank

[MonBarks]

Hi All, I need to loop through a worksheet to extract data while the row is not blank. I'm trying to create a "statement" to see whether invoices have been paid or not. Searching on customer name, extracting invoice amount, how much paid, date paid and have them listed on a separate sheet.
Your help is very welcome, I'm not a VBA programmer. Thank you in advance.

 

[JLGWhiz]

How many columns of data are there in your database and do any of the cells contain formulas that produce a nullstiring ("") if True or False?

 

[MonBarks]

How many columns of data are there in your database and do any of the cells contain formulas that produce a nullstiring ("") if True or False?

Hi JLGWhiz, thank you so much for taking an interest in my plight
I don't think any cells produce a nullstring ??

I think the best way to describe what I would like to achieve is this:
Based on the child's name, cell F4 on sheet A, I would like to list all the records in Sheet B that have data in them, for the Child's name in F4 sheet A. The only data in Sheet B with any relevance to a Statement would be in columns A thru J on sheet B, I think.

Once I have a list on Sheet A of that specific child's invoices, payments, etc. I would like to save it as a pdf and email it to the parent who's email will appear in cell F6 on sheet A.

I hope I'm not asking too much, thank you so much in advance for your help.
Very best regards and keep safe
Paul

 

[JLGWhiz]

this should create your list.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, rw As Long
Set sh1 = Sheets("Weekly Invoice Record")
Set sh2 = Sheets("Create a Statement") 'edit sheet name
    With sh1
        For Each c In .Range("A5", .Cells(Rows.Count, 1).End(xlUp))
            If c.Value = sh2.Range("F4").Value Then
                rw = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
                sh2.Cells(rw, 1).Resize(, 10) = c.Resize(, 10).Value
            End If
        Next
    End With
End Sub

 

[MonBarks]

this should create your list.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, rw As Long
Set sh1 = Sheets("Weekly Invoice Record")
Set sh2 = Sheets("Create a Statement") 'edit sheet name
    With sh1
        For Each c In .Range("A5", .Cells(Rows.Count, 1).End(xlUp))
            If c.Value = sh2.Range("F4").Value Then
                rw = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
                sh2.Cells(rw, 1).Resize(, 10) = c.Resize(, 10).Value
            End If
        Next
    End With
End Sub

 

[MonBarks]

Hi, I copied your code over but it comes up with "Compile error, expected expression"
It doesn't seem to like

Sub t() at the start and End Sub

at the end ???
I do subscribe to Office Pro 365, if that makes any difference??
Kind regards

 

[MonBarks]

Hi again, sorry to be a pest but I'm getting this error. I've checked the worksheet name spellings and they are correct. I don't know what else to do ?? Do I need to add in anything in "tools/References"?
Kind regards

 

[JLGWhiz]

Hi again, sorry to be a pest but I'm getting this error. I've checked the worksheet name spellings and they are correct. I don't know what else to do ?? Do I need to add in anything in "tools/References"?
Kind regards

Check for leading or trailing spaces in the name tab of the sheet.. Try re-typing the sheet name into the name tab. The code ran without error and produced desired results in test setup using the names as shown in the code for sheet names.

 

[MonBarks]

Brilliant - it works a treat
I retyped the sheet name on the sheet and in your vba and it worked as you said.
Thank yo so much
Kind regards
Paul