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

splreece

Board Regular
Joined
May 29, 2015
Messages
72
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top