ashley1984
New Member
- Joined
- Mar 31, 2018
- Messages
- 32
- Office Version
- 365
- Platform
- 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
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 | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
1 | ||||||||||||||||||||||||
2 | 1 | 4 | ||||||||||||||||||||||
3 | 2 | 5 | ||||||||||||||||||||||
4 | 3 | 6 | ||||||||||||||||||||||
5 | ||||||||||||||||||||||||
6 | ||||||||||||||||||||||||
7 | Date: | Mar-21 | ||||||||||||||||||||||
8 | ||||||||||||||||||||||||
9 | ||||||||||||||||||||||||
10 | ||||||||||||||||||||||||
11 | ||||||||||||||||||||||||
12 | ||||||||||||||||||||||||
13 | ||||||||||||||||||||||||
14 | ||||||||||||||||||||||||
15 | 2021 | Comments/Point For Discussion | ||||||||||||||||||||||
16 | Q1 | Q2 | Q3 | Q4 | ||||||||||||||||||||
17 | STRATEGIC PRIORITIES: | LEAD | VALUE/COST (£M) | Start Date | End Date | RAG RATING | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | ||||||
18 | ||||||||||||||||||||||||
19 | New business development plan: | JC | £0.00 | Mar-21 | Mar-22 | |||||||||||||||||||
20 | Complete market audit across grocery retail, QSR’s & FTG specialists | KH | Jan-21 | Jun-21 | A | |||||||||||||||||||
21 | Recruit NBD team | JC | Jan-21 | Dec-21 | R | Needs sign off | ||||||||||||||||||
22 | Assess frozen opportunity | KH | Apr-21 | May-21 | A | |||||||||||||||||||
23 | KH | Apr-21 | Sep-21 | G | ||||||||||||||||||||
24 | Apr-21 | Sep-21 | R | Needs Review | ||||||||||||||||||||
25 | Apr-21 | Sep-21 | G | |||||||||||||||||||||
26 | Apr-21 | Sep-21 | R | Needs Review | ||||||||||||||||||||
27 | Apr-21 | Sep-21 | G | |||||||||||||||||||||
28 | Apr-21 | Dec-21 | ||||||||||||||||||||||
29 | Apr-21 | Sep-21 | R | Capex needed | ||||||||||||||||||||
30 | Apr-21 | Sep-21 | R | Needs sign off | ||||||||||||||||||||
31 | Jan-21 | Dec-21 | G | |||||||||||||||||||||
32 | Apr-21 | Sep-21 | A | |||||||||||||||||||||
33 | Jan-21 | Dec-21 | R | Delay to delivery | ||||||||||||||||||||
34 | Jun-21 | Dec-21 | R | Needs Review | ||||||||||||||||||||
Revolution Kitchen |