Selecting sheet 2

  • Thread starter Thread starter Withnails
  • Start date Start date
W

Withnails

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
 
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
 
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
 
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).PasteSpecial
Next
End Sub

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

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)
 
Note that

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

Will use the activesheet

add the sheet name before Cells
 
Back
Top