code to find text, offset 1 column and paste to new workbook not working

**trillium** · 07-09-2011, 12:36 AM

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?

**JBeaucaire** · 07-09-2011, 01:39 AM

Your code fails because of the .Select on the end, try removing that.

If all you're trying to do is copy all the column J values to the new sheet for the rows that have "Location" in column A, this is what AUTOFILTER was invented for. This method will filter the first sheet leaving only the rows with "Location" showing, then copy the column J values all at once to the Sheet2 in cell A2. No looping.

Dim LR As Long

Workbooks(ThatBook).Sheets("Sheet2").Range("A1:D1").Value = _
    [{"Location","Total Number of Covered Employees","Total Monthly Claim","Rate / Unit:"}]

With Workbooks(ThatBook).Sheets("Sheet1")
    .AutoFilterMode = False             'turn off any prior filters
    .Rows(1).AutoFilter                 'activate the autofilter
    .Rows(1).AutoFilter 1, "Location"   'filter column A
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR > 1 Then .Range("J2:J" & LR).Copy _
        Workbooks(ThatBook).Sheets("Sheet2").Range("A2")
    .AutoFilterMode = False             'turn off filter
End With

**trillium** · 07-09-2011, 08:20 AM

Hi there!

I'd thought about autofilter, but the information on the report output page is not in a chart form, so there are lots of empty spaces inbetween the text. The other titles I want to use are also scattered around on the sheet. Luckily each output has the information always in the same column, so for example, "Location" is always column "A", another one is "J" etc. But above and below the target text, there are empty spaces, autofilter won't work. This way why I was trying to use a loop instead.

:-)

**snb** · 07-09-2011, 08:32 AM

sub snb()
  Sheets("Sheet2").Range("A1:D1") = split("Location|Total Number of Covered Employees|Total Monthly Claim|Rate / Unit:","|")

  with Sheets("Sheet1").usedrange
    .autofilter 1, "Location"
    .offset(1,1).resize(,1).copy sheets("Sheet2").cells(rows.count,1).end(xlup).offset(1)
    .autofilter
  End With
End Sub

**trillium** · 07-09-2011, 06:29 PM

Hi again... autofilter does not work because of all the empty spaces in the column, it just finds blanks underneath... is there any way a loop through the column can work?