Coloured Cells

T

The Message

I have a set of data in sheet 1 and I need to transfer to sheet 2. I have
used VLOOKUP to transfer 10 selected rows of data from sheet 1 which displays
in sheet 2, APART from coloured cells. These cells contain no formula's, they
are just shaded, but VLOOKUP does not transfer this colour (it just displays
0). Is there a way to get the coloured cells to show in sheet 2?
 
J

Jacob Skaria

Not using formulas or UDFs..as they can only return values/calculated results
not formats.... You will need to write a macro to do this.
 
T

The Message

Is this a simple thing to do? Or will it be complicated and lengthy? I've
never written a macro before.
 
J

Jacob Skaria

Post back with sample data or explain how your data is arranged and what is
to be filtered and copied
 
T

The Message

Sheet 1
Top 10 Risk Impact Start Date End Date Previous Traffic
New Traffic

Light Light

1 xxx xxx jan-08 feb-09 shaded
shaded

red blue
xxx xxx mar-09 jun-09 shaded
shaded

green red
2 xxx xxxx may-09 dec-09 shaded
shaded

blue blue
Sheet 2

I am using VLOOKUP to select the top ten assigned rows (ie 1st and 3rd row
from above example) Everything is copying across, apart from the colours.
There are more rows and columns than this. This is a snapshot example.

Cheers
 
J

Jacob Skaria

By top ten if you mean copy all rows having a rank of 1-10 in ColA Sheet1 to
Sheet2; then try the below macro

If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub MyMacro()
Dim lngRow As Long, ws As Worksheet, lngNRow As Long
Set ws = Sheets("Sheet3")
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("A" & lngRow) > 0 And Range("A" & lngRow) <= 10 Then
lngNRow = lngNRow + 1: Rows(lngRow).Copy ws.Rows(lngNRow)
End If
Next
End Sub
 
J

Jacob Skaria

Try with the below data in Sheet1....Rows containing values 1 to 10 will be
copied to Sheet3.

Row Col A
1
2 1
3
4 2
5
6
7 3
8
9
10 4
11
12 5
 
T

The Message

Jacob,

Will this only work with two worksheets. I have data from worksheet 1, being
copied into worksheet 2. Data from worksheet 3, being copied into Worksheet 4
and data from worksheet 5 into 6 and 6 into 7?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top