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?
"Jacob Skaria" wrote:
> 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
>
> --
> Jacob
>
>
> "The Message" wrote:
>
> > Thanks Jacob - I have the message, Subscript out of range!!!
> >
> > "Jacob Skaria" wrote:
> >
> > > 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
> > >
> > > --
> > > Jacob
> > >
> > >
> > > "The Message" wrote:
> > >
> > > > 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" wrote:
> > > >
> > > > > Post back with sample data or explain how your data is arranged and what is
> > > > > to be filtered and copied
> > > > >
> > > > > --
> > > > > Jacob
> > > > >
> > > > >
> > > > > "The Message" wrote:
> > > > >
> > > > > > Is this a simple thing to do? Or will it be complicated and lengthy? I've
> > > > > > never written a macro before.
> > > > > >
> > > > > > "Jacob Skaria" wrote:
> > > > > >
> > > > > > > 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.
> > > > > > >
> > > > > > > --
> > > > > > > Jacob
> > > > > > >
> > > > > > >
> > > > > > > "The Message" wrote:
> > > > > > >
> > > > > > > > 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?
|
Similar Threads
