| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Copying whole rows to different sheet once found
Count = 6
Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
|
#2
|
|||
|
|||
|
Copying whole rows to different sheet once found
Why not sort or autofilter and copy the visible cells all at once. Record a
macro while doing and then clean it up to get rid of the selections which are rarely desirable or necessary. -- Don Guillett SalesAid Software "Gordy w/Hi Expectations" wrote in message ... Count = 6 Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
|
#3
|
|||
|
|||
|
Copying whole rows to different sheet once found
That's what I'm doing now. I put the header on the Toddlers sheet then used
autofilter and copied all the T's to the Toddlers sheet. Would really like all the Rows that have T's automatically go to the Toddlers sheet. "Don Guillett" wrote: Why not sort or autofilter and copy the visible cells all at once. Record a macro while doing and then clean it up to get rid of the selections which are rarely desirable or necessary. -- Don Guillett SalesAid Software "Gordy w/Hi Expectations" wrote in message ... Count = 6 Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
|
#5
|
|||
|
|||
|
Copying whole rows to different sheet once found
recorded. See below for cleanup
Sub Macro8() ' ' Macro8 Macro ' Macro recorded 10/30/2005 by Don Guillett ' ' Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="t" Rows("10:27").Select Selection.Copy Sheets("Sheet8").Select Rows("6:6").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("Sheet7").Select Selection.AutoFilter Range("D3").Select End Sub ======= Sub Macro8fixed()'from source sheet Range("A1").AutoFilter Field:=1, Criteria1:="t" Rows("10:27").Copy Sheets("Sheet8").Rows("6:6") Range("a1").AutoFilter End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Why not sort or autofilter and copy the visible cells all at once. Record a macro while doing and then clean it up to get rid of the selections which are rarely desirable or necessary. -- Don Guillett SalesAid Software "Gordy w/Hi Expectations" wrote in message ... Count = 6 Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
|
#6
|
|||
|
|||
|
Copying whole rows to different sheet once found
I got down to
Worksheets("Classroom Attendance This Week").Activate Range("e6").Select Columns("E:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="T" Then in the recording you copy the rows selected, but they will change over time. I can't seem to get the right thing to copy them automatically. I've tried Selection.Rows.Copy, and Rows(Selection).Copy. "Don Guillett" wrote: recorded. See below for cleanup Sub Macro8() ' ' Macro8 Macro ' Macro recorded 10/30/2005 by Don Guillett ' ' Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="t" Rows("10:27").Select Selection.Copy Sheets("Sheet8").Select Rows("6:6").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("Sheet7").Select Selection.AutoFilter Range("D3").Select End Sub ======= Sub Macro8fixed()'from source sheet Range("A1").AutoFilter Field:=1, Criteria1:="t" Rows("10:27").Copy Sheets("Sheet8").Rows("6:6") Range("a1").AutoFilter End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Why not sort or autofilter and copy the visible cells all at once. Record a macro while doing and then clean it up to get rid of the selections which are rarely desirable or necessary. -- Don Guillett SalesAid Software "Gordy w/Hi Expectations" wrote in message ... Count = 6 Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
|
#7
|
|||
|
|||
|
Copying whole rows to different sheet once found
Hi,
I am doing this off the top of my head and have not tested it but it should work. It uses the count of the columns and rows to establish the whole row and the end point in the column. It copies the whole row to a sheet named "Toddlers". There is no Error handling. sub Toddlers() dim VarX, cell as variant dim strT as string dim intNRows, intCols,intNum as integer strT="T" intRows=0 intCols=0 intNum=0 Sheets("Toddlers").select Range("B2").select set b=selection Sheets("Classroom Attendance This Week").Select Range("E6").Select set e=selection range(e,e.specialcells(xlLastCell).select intRows=selection.rows.count intCols=selection.columns.count e.select range(e,e.offset(intRows,0).select set varX =selection for each cell in varX if cell.value < strT then range(cell,cell.offset(0,intCols)).copy b.offset(intnum,0).paste application.cutcopymode=false intnum=intnum+1 end if next If you want me to write something more suited, send the worksheet to me at confidential info is not shared with any person, living or presumed dead. - - Mark PS see my stuff at http://www.geocities.com/excelmarksway If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend "Gordy w/Hi Expectations" wrote: Count = 6 Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
|
#8
|
|||
|
|||
|
Copying whole rows to different sheet once found
range(e,e.specialcells(xlLastCell).select
range(e,e.offset(intRows,0).select Couldn't run it because of these two formulas. "exceluserforeman" wrote: Hi, I am doing this off the top of my head and have not tested it but it should work. It uses the count of the columns and rows to establish the whole row and the end point in the column. It copies the whole row to a sheet named "Toddlers". There is no Error handling. sub Toddlers() dim VarX, cell as variant dim strT as string dim intNRows, intCols,intNum as integer strT="T" intRows=0 intCols=0 intNum=0 Sheets("Toddlers").select Range("B2").select set b=selection Sheets("Classroom Attendance This Week").Select Range("E6").Select set e=selection range(e,e.specialcells(xlLastCell).select intRows=selection.rows.count intCols=selection.columns.count e.select range(e,e.offset(intRows,0).select set varX =selection for each cell in varX if cell.value < strT then range(cell,cell.offset(0,intCols)).copy b.offset(intnum,0).paste application.cutcopymode=false intnum=intnum+1 end if next If you want me to write something more suited, send the worksheet to me at confidential info is not shared with any person, living or presumed dead. - - Mark PS see my stuff at http://www.geocities.com/excelmarksway If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend "Gordy w/Hi Expectations" wrote: Count = 6 Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
|
#9
|
|||
|
|||
|
Copying whole rows to different sheet once found
Did you look at my "cleaned up" version ?
-- Don Guillett SalesAid Software "Gordy w/Hi Expectations" wrote in message ... I got down to Worksheets("Classroom Attendance This Week").Activate Range("e6").Select Columns("E:E").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="T" Then in the recording you copy the rows selected, but they will change over time. I can't seem to get the right thing to copy them automatically. I've tried Selection.Rows.Copy, and Rows(Selection).Copy. "Don Guillett" wrote: recorded. See below for cleanup Sub Macro8() ' ' Macro8 Macro ' Macro recorded 10/30/2005 by Don Guillett ' ' Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="t" Rows("10:27").Select Selection.Copy Sheets("Sheet8").Select Rows("6:6").Select ActiveSheet.Paste Application.CutCopyMode = False Sheets("Sheet7").Select Selection.AutoFilter Range("D3").Select End Sub ======= Sub Macro8fixed()'from source sheet Range("A1").AutoFilter Field:=1, Criteria1:="t" Rows("10:27").Copy Sheets("Sheet8").Rows("6:6") Range("a1").AutoFilter End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Why not sort or autofilter and copy the visible cells all at once. Record a macro while doing and then clean it up to get rid of the selections which are rarely desirable or necessary. -- Don Guillett SalesAid Software "Gordy w/Hi Expectations" wrote in message ... Count = 6 Sheets("Classroom Attendance This Week").Select Range("e6").Select If ActiveCell.Value = "T" Then Return 'Exit Sub While ActiveCell.Value < "T" ActiveCell.Offset(1, 0).Activate Count = Count + 1 Wend It's easy enough to find the first one of the (T)oddlers but I need to copy the whole row to another sheet automatically. Plus I need to find all the "T" in the 297 rows and preferrably copy all the rows at once without blanks between rows. Help would be good. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving rows to sequential position on another sheet | Excel Discussion (Misc queries) | |||
| Copying chart to another sheet | Excel Worksheet Functions | |||
| Hid rows in a hidden sheet. | Excel Discussion (Misc queries) | |||
| Copying whole rows based upon one criteria | Excel Discussion (Misc queries) | |||
| 2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
Linear Mode
