Excel VBA to Copy and Paste Rows if conditions is met

ashley1984

New Member
Joined
Mar 31, 2018
Messages
32
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm trying to run VBA that copies a cell (W21 in this case) if a condition is met in a different cell (column J) and if the value in column J = R on the sheet named Revolution Kitchen. I then want to copy the text in cell W and past it in 1 of 6 boxes between the range Q2:4 & T2:4 (Yellow cells in the image)

There could be more than 6 cells with "R" but I only want the first 6 to be copied and pasted into Q2:4 & T2:4.

I've managed to write the code to select the cell in row W if the condition is R in row J, and past the text in W but can't get the text to past in a range Q2:4 & T2:4

Private Sub CommandButton1_Click()

lastRow = Worksheets("Revolution Kitchen").Range("J" & Rows.Count).End(xlUp).Row

For r = 20 To lastRow
If Worksheets("Revolution Kitchen").Range("J" & r).Value = "R" Then

Worksheets("Revolution Kitchen").Range("W" & r).Copy

Worksheets("Revolution Kitchen").Activate
lastRowRpt = Worksheets("Revolution Kitchen").Range("J" & Rows.Count).End(xlUp).Row
Worksheets("Revolution Kitchen").Range("Q" & lastRowRpt).Select

ActiveSheet.Paste

End If


Next r


End Sub

Governance Template V0.3 - 21.3.21.xlsm
BCDEFGHIJKLMNOPQRSTUVW
1
214
325
436
5
6
7Date:Mar-21
8
9
10
11
12
13
14
152021Comments/Point For Discussion
16Q1Q2Q3Q4
17STRATEGIC PRIORITIES:LEADVALUE/COST (£M)Start DateEnd DateRAG RATINGJanFebMarAprMayJunJulAugSepOctNovDec
18
19New business development plan:JC£0.00Mar-21Mar-22
20Complete market audit across grocery retail, QSR’s & FTG specialistsKHJan-21Jun-21A
21Recruit NBD team JCJan-21Dec-21RNeeds sign off
22Assess frozen opportunityKHApr-21May-21A
23KHApr-21Sep-21G
24Apr-21Sep-21RNeeds Review
25Apr-21Sep-21G
26Apr-21Sep-21RNeeds Review
27Apr-21Sep-21G
28Apr-21Dec-21
29Apr-21Sep-21RCapex needed
30Apr-21Sep-21RNeeds sign off
31Jan-21Dec-21G
32Apr-21Sep-21A
33Jan-21Dec-21RDelay to delivery
34Jun-21Dec-21RNeeds Review
Revolution Kitchen
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Why not just use a formlua?
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1
21Needs sign off4Needs sign off
32Needs Review5Delay to delivery
43Capex needed6 
5
6
7Date:21/03/2021
8
9
10
11
12
13
14
152021Comments/Point For Discussion
16Q1Q2Q3Q4
17STRATEGIC PRIORITIES:LEADVALUE/COST (£M)Start DateEnd DateRAG RATING441974425544286443164434744377444084443944469445004453044561
18
19New business development plan:JC3541164427844642
20Complete market audit across grocery retail, QSR’s & FTG specialistsKH4419744348A
21Recruit NBD team JC4419744531RNeeds sign off
22Assess frozen opportunityKH4428744318A
23KH4428744440G
244428744440RNeeds Review
254428744440G
264428744440Needs Review
274428744440G
284428744542
294428744440RCapex needed
304428744440RNeeds sign off
314419744531G
324428744440A
334419744531RDelay to delivery
344435144542Needs Review
35
Master
Cell Formulas
RangeFormula
Q2:Q4,T2:T4Q2=IFERROR(INDEX($W$19:$W$34,AGGREGATE(15,6,(ROW($W$19:$W$34)-ROW($W$19)+1)/($J$19:$J$34="R"),P2)),"")
C7C7=TODAY()
G19G19=SUM(H20:H27)
 
Upvote 0
Solution
Why not just use a formlua?
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1
21Needs sign off4Needs sign off
32Needs Review5Delay to delivery
43Capex needed6 
5
6
7Date:21/03/2021
8
9
10
11
12
13
14
152021Comments/Point For Discussion
16Q1Q2Q3Q4
17STRATEGIC PRIORITIES:LEADVALUE/COST (£M)Start DateEnd DateRAG RATING441974425544286443164434744377444084443944469445004453044561
18
19New business development plan:JC3541164427844642
20Complete market audit across grocery retail, QSR’s & FTG specialistsKH4419744348A
21Recruit NBD team JC4419744531RNeeds sign off
22Assess frozen opportunityKH4428744318A
23KH4428744440G
244428744440RNeeds Review
254428744440G
264428744440Needs Review
274428744440G
284428744542
294428744440RCapex needed
304428744440RNeeds sign off
314419744531G
324428744440A
334419744531RDelay to delivery
344435144542Needs Review
35
Master
Cell Formulas
RangeFormula
Q2:Q4,T2:T4Q2=IFERROR(INDEX($W$19:$W$34,AGGREGATE(15,6,(ROW($W$19:$W$34)-ROW($W$19)+1)/($J$19:$J$34="R"),P2)),"")
C7C7=TODAY()
G19G19=SUM(H20:H27)
Because I like to complicate things :)

This is great, thank you
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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