Select range based on cell value in a column and copy tranpose.

[luzikedy]

Hi there,

I would like to ask you for code that will do the following:

Select range A1:A4 in a sheet "JIN"

Copy Transpose

Move to worksheet "Obsada"

Look for value "1" in a column "W"

Select Range A:D in a row where value "1" was found (There is one value 1 in a sheet in row "W")

Paste transpose from from sheet "Jin"


Thank you in advance!

 

[KeepTrying]

Hi,

My understanding is that you want to paste mentioned range in the same row where value 1 is found on sheet "Obsada". Please see code below, I hope it'll work for you:

Sub CopyTranspose()
Dim Cella
Dim RowID, RowEnd
Application.ScreenUpdating = False
RowID = -1
RowEnd = Sheets("Obsada").UsedRange.Rows.Count
For Each Cella In Sheets("Obsada").Range("w1:w" & RowEnd).Cells
    If Cella.Value = 1 Then
        RowID = Cella.Row
        Sheets("JIN").Range("a1:a4").Copy
        Sheets("Obsada").Cells(RowID, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
        Exit For
    End If
Next Cella
If RowID = -1 Then
    MsgBox "Value 1 not found in column W on sheet 'Obsada'"
    Exit Sub
End If
Application.ScreenUpdating = True
End Sub

Regards,

 

[luzikedy]

Thank you a lot, it works like a charm. I have no further questions Your Honor

 

[KeepTrying]

I'm glad I could help

Have a nice day.