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

luzikedy

New Member
Joined
May 23, 2014
Messages
44
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!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

KeepTrying

Active Member
Joined
Aug 19, 2012
Messages
264
Office Version
365, 2010
Platform
Windows
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:

Code:
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,
 
Last edited:

luzikedy

New Member
Joined
May 23, 2014
Messages
44
Thank you a lot, it works like a charm. I have no further questions Your Honor :)
 

KeepTrying

Active Member
Joined
Aug 19, 2012
Messages
264
Office Version
365, 2010
Platform
Windows
I'm glad I could help :)

Have a nice day.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,396
Messages
5,444,233
Members
405,275
Latest member
RefreshSQL

This Week's Hot Topics

Top