VBA Coding Questions on Copy and Paste

[rickloudin]

I am new to VBA coding, Want to know if you can copy and paste cells from cells in the row above the cells that meet a certain criteria.
For example A3 meets my criteria and I want to paste the Range B2:E2 into B3:E3. The next time the criteria appears maybe in A8 so I would paste B7:E7 to B8:E8 and so on.
Any help would be greatly appreciated. Thank you!

 

[My Aswer Is This]

I am new to VBA coding, Want to know if you can copy and paste cells from cells in the row above the cells that meet a certain criteria.
For example A3 meets my criteria and I want to paste the Range B2:E2 into B3:E3. The next time the criteria appears maybe in A8 so I would paste B7:E7 to B8:E8 and so on.
Any help would be greatly appreciated. Thank you!

Yes this can be done.
Provide more exact details.
Like do you want a script to look down column A and look for some criteria and perform this past action for all cells in column A that meet a certain criteria?

 

[kennypete]

Welcome @rickloudin

Something like this?

Book2
	A	B	C	D	E
1
2	N	B	C	D	E
3	Y
4	N
5	N
6	N
7	N	2	3	4	5
8	Y
9	N
Sheet1

The VBA code, "copying" the values from the B-E columns from the row above, where the criterion met is the cell in column A = "Y":

Sub subCopyYitems()

Dim lngLoop As Long

For lngLoop = 2 To 9
    If Cells(lngLoop, 1) = "Y" Then
        Range("B" & Trim(Str(lngLoop - 1)), "E" & Trim(Str(lngLoop - 1))).Copy _
            Destination:=Range("B" & Trim(Str(lngLoop)), "E" & Trim(Str(lngLoop)))
    End If
Next lngLoop

End Sub

 

[My Aswer Is This]

Here is a script that would do what you want.
If in column A your search for the value "Alpha"

Sub Look_For_Criteria()
'Modified 4/24/2021  1:03:45 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Alpha" Then Cells(i, 1).Offset(-1, 1).Resize(, 4).Copy Cells(i, 1).Offset(, 1)
    Next
Application.ScreenUpdating = True
End Sub

 

[rickloudin]

Welcome @rickloudin

Something like this?

Book2
	A	B	C	D	E
1
2	N	B	C	D	E
3	Y
4	N
5	N
6	N
7	N	2	3	4	5
8	Y
9	N
Sheet1

The VBA code, "copying" the values from the B-E columns from the row above, where the criterion met is the cell in column A = "Y":

Sub subCopyYitems()

Dim lngLoop As Long

For lngLoop = 2 To 9
    If Cells(lngLoop, 1) = "Y" Then
        Range("B" & Trim(Str(lngLoop - 1)), "E" & Trim(Str(lngLoop - 1))).Copy _
            Destination:=Range("B" & Trim(Str(lngLoop)), "E" & Trim(Str(lngLoop)))
    End If
Next lngLoop

End Sub

Thank you!

 

[rickloudin]

Here is a script that would do what you want.
If in column A your search for the value "Alpha"

Sub Look_For_Criteria()
'Modified 4/24/2021  1:03:45 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Alpha" Then Cells(i, 1).Offset(-1, 1).Resize(, 4).Copy Cells(i, 1).Offset(, 1)
    Next
Application.ScreenUpdating = True
End Sub

Thank you! What do I need to do it get it to work for my criteria "Bud2020". Works very well if I change by criteria to "alpha" but when i change to my Bud2020 it will not work.

 

[My Aswer Is This]

So you have this exact value in column A:
"Bud2020"
And this value is entered manually and not the result of a formula.
show me the code you used that did not work.
And what do you mean by did not work.
Did you get a error message?
And the script provide in post 3 did not work?

 

[rickloudin]

Thank you!

So you have this exact value in column A:
"Bud2020"
And this value is entered manually and not the result of a formula.
show me the code you used that did not work.
And what do you mean by did not work.
Did you get a error message?
And the script provide in post 3 did not work?

 

[rickloudin]

I put them in manually. And the macro runs perfectly.
Thank you!

 

[My Aswer Is This]

I put them in manually. And the macro runs perfectly.
Thank you!

Glad you have things working now. Not sure what script worked perfectly.
There were two of us providing code.