Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting sheet with VB | Excel Discussion (Misc queries) | |||
selecting sheet name in another workbook by variable (same sheet name) | Excel Programming | |||
Print a sheet without ever selecting the sheet | Excel Programming | |||
Selecting Last Sheet | Excel Worksheet Functions | |||
Selecting sheet | Excel Programming |