Copy and paste to next empty row in another sheet and transpose - based on colour

[SleightOfHand]

Hi,

I'm almost there trying to copy and paste but I can't seem to find the right solution.

I've got a column that starts from E7 and goes to E100. It contains a few different inputs, but also has many yellow cells that I want to paste into another sheet. This sheet would need to be on the next available ROW that doesn't have data starting from the first cell of that row. This would be that I can edit those yellow cells and log them into another sheet. Lastly, the data would need to be transposed. What I've got at the moment:

Sub Copy()
Dim Data as Range
     For Each Data in Sheets("No1").Range("E2:E100")
     If Data.interior.Color = RGB(255, 255, 183) Then
     Data.Copy

    Sheets("Out").Range("A" & Rows.Count).End(xlUp).Offset(1,0).PasteSpecial Paste:=xlPasteValues
End If

Next Data
End Sub

Adding transpose:= true doesn't seem to work.

Another thing is that this code just freezes up the sheets themselves although it does copy what I want.

Cheers!

 

[My Aswer Is This]

Why do you think we need to use transpose?
We are copying a entire row on one sheet and pasting that same row into another sheet.

 

[SleightOfHand]

I am copying a column and want to transpose that to a row in another sheet.

 

[My Aswer Is This]

I am copying a column and want to transpose that to a row in another sheet.

So if we find a yellow cell in E25
What part of that column do you want to copy?

 

[SleightOfHand]

At the moment, this is what the inputs are in the first sheet

Copy of Copy of Future States Input Template_v1.3.xlsm
	E
7	a
8	b
9	c
v2

This is Column E in the first Sheet. Running the command pastes it in a column on the next sheet, but I want it across the row instead, to look like the following:

Copy of Copy of Future States Input Template_v1.3.xlsm
	A	B	C
3	a	b	c
Log

I want to copy everything within the yellow cells within the Column E.

 

[My Aswer Is This]

You cannot copy and entire column and paste it into a Row.

A column has about 1.5 million cells.
A Row only has about 16,000 cells

 

[SleightOfHand]

But I don't want to copy the entire column; there are about 100 cells in the column and I want to copy the ones that are yellow. I've used the transpose feature for xlPaste before and it has worked, but here when I define to copy by colour, it doesn't.

 

[My Aswer Is This]

So any cell which is yellow you want to copy into Column A on other sheet is that true?

 

[SleightOfHand]

Yes, that's what I'm trying to go for.. thanks

The VBA code above works fine, but it doesn't transpose the data even if I were to put the command there after. So I'm trying to find out how to do so / why it's not working

 

[My Aswer Is This]

Yes, that's what I'm trying to go for.. thanks

The VBA code above works fine, but it doesn't transpose the data even if I were to put the command there after. So I'm trying to find out how to do so / why it's not working

Got it. Will have answer soon