Copy value based on criteria of different cell

[Saoirse]

I am trying to create a macro that automatically copies data from sheet 1 to sheet 2 based on the data in sheet 1.
However, everyday I change the information in sheet 1. So I am trying to create a macro I can run to have the info pull daily.

I want columns C and D to copy over to the cooresponding sheet of the employee. So sheet 1 A2 has Albert with a Q and a P column. I am trying to get B2 and C2 to transfer over to the "Albert" sheet but only if they coorespond with Albert on sheet 1.

bench(153).xlsm
	A	B	C
1	Albert
2	I want this cell to have yesterday's date	This cell should copy the data from Sheet 1 column B but only when column A says "Albert"	This cell should copy the data from Sheet 1 column C but only when column A says "Albert"
3	Previous Date	Queued	Pended
4	1/30/2022
5
6
Albert

Cell Formulas
Range	Formula
A4	A4	=TODAY()-1

bench(153).xlsm
	A	B	C
1	UserID	Q	P
2	Albert	126	436
3	Bradley	27	236
4	Conner	81	334
5	Deanna	87	315
6	Eli	65	81
7	Felicia	46	298
8	Greg	142	254
9	Henry	22	134
10	Isacc	23	314
11	Jeremy	84	403
12	Kyle	18
13	Lawrance	102	151
14	Mary	11	359
15	Nancy	49	274
16	Oscar	49	229
17	Peter	17	535
18	Quincy	164	285
19	Robert	158	149
20	Sam	35	78
Sheet 1

 

[Jamie McMillan]

Hello Saoirse,

The below code should do what you want.

Sub CopyCD()
Application.ScreenUpdating = False 'Turns off screen updating - runs faster
Dim i As Integer
i = 1  ' i is the row number that changes by 1 for each loop

For i = 1 To 20 'loops from A1 to A20 (You can change that to the last row you have)
    If Cells(i, 1).Value = Sheets("Data").Cells(i, 1).Value Then 'checks the A1 values match
    Sheets("Data").Cells(i, 2).Copy 'Copies B2
    Cells(i, 2).Select 'Selects and pastes values to B2
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Cells(i, 3).Copy 'Copies C2
    Cells(i, 3).Select 'Selects and pastes values to B2
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
Next i 'Loops through rows 2,3,4,5,..20
Application.CutCopyMode = False
Application.ScreenUpdating = True 'Turns on screen updating again
End Sub

Jamie

 

[Saoirse]

Unfortunately it didn't work I'm not sure where I went wrong, but no luck.

 

[Jamie McMillan]

Hello,

Did you change where it says - Sheets("Data"). to Sheets("Albert"). for every instance?

Jamie

 

[Saoirse]

Sub CopyCD()
Application.ScreenUpdating = False
Dim i As Integer
i = 1

For i = 1 To 20
If Cells(i, 1).Value = Sheets("Albert").Cells(i, 1).Value Then
Sheets("Albert").Cells(i, 2).Copy
Cells(i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Albert").Cells(i, 3).Copy
Cells(i, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

bench(153).xlsm
	A	B	C
1	Albert
2	I want this cell to have yesterday's date	This cell should copy the data from Sheet 1 column B but only when column A says "Albert"	This cell should copy the data from Sheet 1 column C but only when column A says "Albert"
3	Previous Date	Queued	Pended
4	2/2/2022
5
6
Albert

Cell Formulas
Range	Formula
A4	A4	=TODAY()-1

bench(153).xlsm
	A	B	C
1	UserID	Q	P
2	Albert	126	436
3	Bradley	27	236
4	Conner	81	334
5	Deanna	87	315
6	Eli	65	81
7	Felicia	46	298
8	Greg	142	254
9	Henry	22	134
10	Isacc	23	314
11	Jeremy	84	403
12	Kyle	18
13	Lawrance	102	151
14	Mary	11	359
15	Nancy	49	274
16	Oscar	49	229
17	Peter	17	535
18	Quincy	164	285
19	Robert	158	149
20	Sam	35	78
21	Timothy	112	168
22	Ursula	5	53
Sheet 1

 

[Jamie McMillan]

Hello,

I think it is backwards.

The macro 'button
will be on the Albert tab.

And then replace Each "ALBERT" in the code with the name of the sheet with the user id column.

The first part of the loop reads like this

If Cells(i, 1).Value = Sheets("Albert").Cells(i, 1).Value Then     'if the cell on THIS sheet = the cell on the other sheet then do this
Sheets("Albert").Cells(i, 2).Copy                                              'Copy the cell on the other sheet
Cells(i, 2).Select                                                                       'Paste the values of the cell on the THIS sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

NB:- The loop should be 2-20 that will start on cell A2 - 1 to 20 starts on A1. My mistake.

Jamie

 

[Saoirse]

no luck again. will send screen shots once on other computer. This computer doesn't do the xl2bb thing. will send in a few hours.

 

[Saoirse]

still unable to get columns C and D to copy over to associated sheet.

Sub CopyCD()
Application.ScreenUpdating = False
Dim i As Integer
i = 1

For i = 1 To 20
If Cells(i, 1).Value = Sheets("Albert").Cells(i, 1).Value Then
Sheets("Albert").Cells(i, 2).Copy
Cells(i, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Albert").Cells(i, 3).Copy
Cells(i, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

bench.xlsm
	A	B	C
1	UserID	Q	P
2	Albert	126	436
3	Bradley	27	236
4	Conner	81	334
5	Deanna	87	315
6	Eli	65	81
7	Felicia	46	298
8	Greg	142	254
9	Henry	22	134
10	Isacc	23	314
11	Jeremy	84	403
12	Kyle	18
13	Lawrance	102	151
14	Mary	11	359
15	Nancy	49	274
16	Oscar	49	229
17	Peter	17	535
18	Quincy	164	285
19	Robert	158	149
20	Sam	35	78
21	Timothy	112	168
22	Ursula	5	53
Sheet 1

bench.xlsm
	A	B	C
1	Albert
2	I want this cell to have yesterday's date	This cell should copy the data from Sheet 1 column B but only when column A says "Albert"	This cell should copy the data from Sheet 1 column C but only when column A says "Albert"
3	Previous Date	Queued	Pended
4	2/6/2022
5
Albert

Cell Formulas
Range	Formula
A4	A4	=TODAY()-1