Announcement

Collapse
No announcement yet.

Macro to copy rows based on a condition overwrites previous results

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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



    Hi,

    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
    Do
    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 :

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

    Comment


    • #3


      Worked perfectly, thanks a lot!

      Comment

      Working...
      X