referencing query in VBA - copy a row based on referenced cell contents

[splreece]

morning all,

I have the following code that should take the cell contents from "O5" and search column B until its found.
Then copy that row to "WIPstore" tab.


(if it helps).

Each row is a Week Ending date and resulting figures for that day.

Cell O5 is a formula that works out the previous Fridays date based on todays date.


so I am simply looking to match column B against the date in cell "O5" and copy the entire row matched.


Any thoughts?


Sub WIPCopy()
Dim rng As Range
rng = Range("05").Value
rng.Select
Sheets("WIP").Select
For Each Cell In Sheets(1).Range("B:B")
If Cell.Value = rng Then
matchRow = Cell.Row
Rows(matchRow & ":" & matchRow).Select
Selection.copy
Sheets("WIPStore").Select
ActiveSheet.Rows(matchRow).Select
ActiveSheet.Paste
Sheets("WIP").Select
End If
Next
End Sub

 

[baitmaster]

Several things. You've written range (zero 5) instead of O5. You've referred to the range object's value but not SET it as the range object - and your description suggests it isn't. Plus you're looping through every cell in column B when you don't need to, and selecting ranges - never select stuff unless absolutely necessary

Try

Sub WIPCopy()
Dim matchRow As Long: matchRow = Application.Match(Range("O5").Value, Sheets(1).Range("B:B"), 0)
Sheets("WIP").Rows(matchRow).Copy
Sheets("WIPStore").Cells(matchRow, 1).PasteSpecial xlPasteAll
End Sub