newB February 2016

VBA copy paste code only returns first hit from specified criteria

I am trying to copy rows from one excel sheet to another using a criteria. The desired result: VBA code finds all the matches and pastes them without spaces into a table. When a user changes a criteria and hits run, all the old results get removed and new list matching criteria appears.

I have the following code. When I run it though only the first record which matches the criteria appears. HELP! What am I doing wrong?!

'Module to search all Wards' records that match a Ward name specified in the drop-down menu

Sub findward()
Dim wardname As String
Dim finalrow As Integer
Dim i As Integer 

Sheets("Ward_rank_table").Range("B7:BC157").ClearContents
wardname = Sheets("Ward_rank_table").Range("B3").Value
finalrow = Sheets("Ward_rank_set").Range("B160").End(xlUp).Row

Sheets("Ward_rank_set").Select
For i = 2 To finalrow
    If Cells(i, 2) = wardname Then
    Range(Cells(i, 2), Cells(i, 55)).Copy
    Sheets("Ward_rank_table").Select
    Range("B7").End(xlUp).Offset(1, 0).Resize(1, 55).PasteSpecial xlPasteFormulasAndNumberFormats

    End If


Next i

Range("B3").Select



End Sub

Answers


Slubee February 2016

Try: sheets("Ward_rank_set").select finalrow = ActiveSheet.UsedRange.Rows.count


Vityata February 2016

The problem is that you use the select rather creepy, thus once you do the first copy, you stay on "Ward_rank_table" and you keep on searching on it. Probably there is nothing there, thus it does not find anything. A working code (and a really bad practise) would be:

Sheets("Ward_rank_set").Select
For i = 2 To finalrow
    If Cells(i, 2) = wardname Then
        Range(Cells(i, 2), Cells(i, 55)).Copy
        Sheets("Ward_rank_table").Select
        Range("B7").End(xlUp).Offset(1, 0).Resize(1, 55).PasteSpecial xlPasteFormulasAndNumberFormats

    End If

    Sheets("Ward_rank_set").Select
Next i

In general - avoid the usage of SELECT and ACTIVATE in VBA. As much as possible.

Post Status

Asked in February 2016
Viewed 3,323 times
Voted 8
Answered 2 times

Search




Leave an answer