Hi there

I'm working with a report that has text and many empty cells. However, periodically the word "Location" appears in column A. I would like to copy what appears next to that word (column B) and paste it in the next empty cell on another sheet under the word "Location".

Basically, I am searching a report output and trying to make a Database summary of the information onto another tab to make it easier to read, etc.

I'm struggling with trying to find a match for text in a column though... 

Workbooks(ThatBook).Activate
    
Sheets("Sheet2").Select
Range("A1").Value = "Location"
Range("B1").Value = "Total Number of Covered Employees "
Range("C1").Value = "Total Monthly Claim"
Range("D1").Value = "Rate / Unit:"

Sheets("Sheet1").Activate

    For Each c In Columns("A:A").Select
        If c.Value = "Location" Then
            Range(Cells(c.lRow, 10)).Copy

              lRow = lRow + 1
            ActiveSheet.Paste Destination:=Worksheets("Sheet2").Cells(lRow, 1)
        End If
    Next c
I highlighted in Red where the marco bugs and gives a Runtime 424: Object required error, which I can't seem to solve.

OK, in the preview it doesn't show. This is the line it stops at:
For Each c In Columns("A:A").Select
        If c.Value = "Location" Then
I will be going through other columns to capture the other info and paste it as well.

Thoughts?