No announcement yet.

Macro to copy rows based on a condition overwrites previous results

  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro to copy rows based on a condition overwrites previous results


    I have an Excel document containing a large amount of different worksheets.
    I started writing a Macro to search the different worksheets for a CustomerNumber in Column C and copy the whole row to another sheet when the CustomerNumber was found.
    However, not all of the rows contain data in column a, thus everytime the Macro copies a row without containing data in column a it simply overwrites the data with the next result.
    Is there any way to fix this? I tried several Things but can't seem to make it work.

    Any help is much appreciated!

    Sub SearchSheets()
    Dim FirstAddress As String, WhatFor As String
    Dim Cell As Range, Sheet As Worksheet
    WhatFor = InputBox("Insert CustomerNo", "Search Criteria")
    If WhatFor = Empty Then Exit Sub
    For Each Sheet In Sheets
    If Sheet.Name <> "SEARCH" Then
    With Sheet.Columns(3)
    Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlPart)
    If Not Cell Is Nothing Then
    FirstAddress = Cell.Address
    Cell.EntireRow.Copy _
    Destination:=Sheets("SEARCH").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

    Set Cell = .FindNext(Cell)
    Loop Until Cell.Address = FirstAddress

    End If
    End With
    End If
    Next Sheet
    Set Cell = Nothing
    End Sub

  • #2
    You would need to replace the column in your destination range to something that you can guarantee will always have data (column B maybe?) then you can use offset to nudge it back to the cell in column a.

    So maybe something like :

    Cell.EntireRow.Copy Destination:=Sheets("SEARCH").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)


    • #3

      Worked perfectly, thanks a lot!