Selecting sheet 2
 

I have a Sub that grabs data from sheet1 and i cant get it to grab the data
(in column K) from sheet2, even when i add Worksheet("Sheet2") as shown
below...?

How can I use this macro to grab the data from sheet2, not sheet1?

Sub ReLargeMoves()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy
Sheets("Sheet3").Range("A15").Rows(lngRow)
Next
End Sub

Selecting sheet 2
 

Try

Sub ReFormat2()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & _
Cells(Cells.Rows.Count, "K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Worksheets("Sheet2").Rows(c.Row).Copy _
Sheets("Sheet3").Rows(lngRow)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

I have a Sub that grabs data from sheet1 and i cant get it to grab the data
(in column K) from sheet2, even when i add Worksheet("Sheet2") as shown
below...?

How can I use this macro to grab the data from sheet2, not sheet1?

Sub ReLargeMoves()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy
Sheets("Sheet3").Range("A15").Rows(lngRow)
Next
End Sub

Selecting sheet 2
 

Check your previous post. Replace

<lngRow = lngRow + 1: Rows(c.Row).Copy
<Sheets("Sheet3").Range("A15").Rows(lngRow

with

lngRow = lngRow + 1: Sheets("Sheet2").Rows(c.Row).Copy _
Sheets("Sheet3").Range("A15").Rows(lngRow)

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

I have a Sub that grabs data from sheet1 and i cant get it to grab the data
(in column K) from sheet2, even when i add Worksheet("Sheet2") as shown
below...?

How can I use this macro to grab the data from sheet2, not sheet1?

Sub ReLargeMoves()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy
Sheets("Sheet3").Range("A15").Rows(lngRow)
Next
End Sub

Selecting sheet 2
 

Hi,

This works fine for me

Sub ReLargeMoves()
Sheets("Sheet2").Activate
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)

For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy
Sheets("Sheet3").Range("A15").Rows(lngRow).PasteSp ecial
Next
End Sub

Mike

"Withnails" wrote:

I have a Sub that grabs data from sheet1 and i cant get it to grab the data
(in column K) from sheet2, even when i add Worksheet("Sheet2") as shown
below...?

How can I use this macro to grab the data from sheet2, not sheet1?

Sub ReLargeMoves()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy
Sheets("Sheet3").Range("A15").Rows(lngRow)
Next
End Sub

Selecting sheet 2
 

Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)

I see a possible problem with the line of code above. While the range being
assigned to the rng variable is from Sheet2, the "last row" calculation will
be from the active sheet at the time the code line is executed. Why? Because
the Cells property call does not reference Sheet2, so its chain of
properties will draw from the active sheet and thus the last row in Column K
on the active sheet is what will be concatenated onto your "K2:K" address.
You can solve this problem by concatenating the worksheet reference onto to
it...

Set rng = Worksheets("Sheet2").Range("K2:K" & Worksheets("Sheet2"). _
Cells(Cells.Rows.Count, "K").End(xlUp).Row)

--
Rick (MVP - Excel)


"Withnails" wrote in message
...
I have a Sub that grabs data from sheet1 and i cant get it to grab the data
(in column K) from sheet2, even when i add Worksheet("Sheet2") as shown
below...?

How can I use this macro to grab the data from sheet2, not sheet1?

Sub ReLargeMoves()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy
Sheets("Sheet3").Range("A15").Rows(lngRow)
Next
End Sub

Selecting sheet 2
 

Note that

Cells(Cells.Rows.Count,
"K").End(xlUp).Row)

Will use the activesheet

add the sheet name before Cells



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Withnails" wrote in message ...
I have a Sub that grabs data from sheet1 and i cant get it to grab the data
(in column K) from sheet2, even when i add Worksheet("Sheet2") as shown
below...?

How can I use this macro to grab the data from sheet2, not sheet1?

Sub ReLargeMoves()
Dim c As Range, rng As Range, lngRow As Long
Set rng = Worksheets("Sheet2").Range("K2:K" & Cells(Cells.Rows.Count,
"K").End(xlUp).Row)
For Each c In rng
If WorksheetFunction.Large(rng, 1) = c.Value Or _
WorksheetFunction.Large(rng, 2) = c.Value Or _
WorksheetFunction.Large(rng, 3) = c.Value Or _
WorksheetFunction.Small(rng, 1) = c.Value Or _
WorksheetFunction.Small(rng, 2) = c.Value Or _
WorksheetFunction.Small(rng, 3) = c.Value Then _
lngRow = lngRow + 1: Rows(c.Row).Copy
Sheets("Sheet3").Range("A15").Rows(lngRow)
Next
End Sub