VBA copy cell to same row if criteria match

[kitsa]

Hi,
I'm looking to see how I create VBA to copy cell value from 1 cell to another cell in same row, of criteria e.g. "S" or "V".
As per pic:
If Cell B:B = "S" or "V", copy Cell G:G to Cell J:J on same row.

 

[My Aswer Is This]

Try this:

Sub Copy_If_S_Or_V()
'Modified  5/9/2022  10:37:54 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    For i = 1 To Lastrow
        Select Case Cells(i, 2).Value
            Case "S", "V": Cells(i, "G").Copy Cells(i, "J")
        End Select
    Next
Application.ScreenUpdating = True
End Sub

 

[kitsa]

Try this:

Sub Copy_If_S_Or_V()
'Modified  5/9/2022  10:37:54 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    For i = 1 To Lastrow
        Select Case Cells(i, 2).Value
            Case "S", "V": Cells(i, "G").Copy Cells(i, "J")
        End Select
    Next
Application.ScreenUpdating = True
End Sub

Hi,
I tried the code and it works, but when I tried on other spreadsheet it doesn't work due to the cap between data as show below.
Also I want to go to another sheet and it doesn't work once it goes to the other sheet.
I created a command button and put the code in Design Mode, is that correct?

 

[kitsa]

I also noticed I made a mistake on my request. I need the copy to be paste special.
"Case "S", "V": Cells(i, "I").Copy Cells(i, "J")"

 

[My Aswer Is This]

Well, if the script worked on one worksheet but not on another.
It's not because of my script.
You must have things laid out differently on other sheet

And I do not understand this:
I tried on other spreadsheet it doesn't work due to the cap between data as show below.
what is cap?

And why Pastespecial

 

[My Aswer Is This]

Well, if the script worked on one worksheet but not on another.
It's not because of my script.
You must have things laid out differently on other sheet

And I do not understand this:
I tried on other spreadsheet it doesn't work due to the cap between data as show below.
what is cap?

And why Pastespecial

Pastespecial has a lot of possibilities
So tell me what is wrong with copy

 

[My Aswer Is This]

And what does this mean?
You said:
Also, I want to go to another sheet and it doesn't work once it goes to the other sheet.

Do you mean you want it copied to another sheet?
And if so what is name of other sheet
And you never mentioned this in your original post

 

[kitsa]

Sorry all, I mean due to the gap, it doesn't work.
I need to put a command tab on "Sheet1" and for the VBA to work on "Sheet2"
What I need is copy from Column "I" and paste special to Column "J" (with no formula).
Hope this makes sense?

Thanks
Kitsa

 

[My Aswer Is This]

In your original post you said:
If Cell B:B = "S" or "V", copy Cell G:G to Cell J:J on same row.

But in your last post you said:
What I need is copy from Column "I" and paste special to Column "J" (with no formula).

So what is it Copy column "I" or column "G"

 

[My Aswer Is This]

This script here assumes we are dealing Column G and Not Column I

In the script Change G to I if you want:

Sub Copy_If_S_Or_V()
'Modified  5/10/2022  9:11:09 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row
    With Sheet2
        For i = 1 To Lastrow
            Select Case .Cells(i, 2).Value
                Case "S", "V": .Cells(i, "J").Value = .Cells(i, "G").Value
            End Select
        Next
    End With
Application.ScreenUpdating = True
End Sub