Coloured Cells

[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?

 

[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.

 

[The Message]

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

 

[Jacob Skaria]

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

 

[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

 

[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

 

[The Message]

Thanks Jacob - I have the message, Subscript out of range!!!

 

[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

 

[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?