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

SleightOfHand

New Member
Joined
Jun 19, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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!
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,431
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jun 19, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I am copying a column and want to transpose that to a row in another sheet.
 

SleightOfHand

New Member
Joined
Jun 19, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
7a
8b
9c
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
ABC
3abc
Log


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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,431
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jun 19, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,431
Office Version
  1. 2013
Platform
  1. Windows
So any cell which is yellow you want to copy into Column A on other sheet is that true?
 

SleightOfHand

New Member
Joined
Jun 19, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,431
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,935
Members
416,143
Latest member
JoyceMB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top