Copying whole rows to different sheet once found

  • Thread starter Gordy w/Hi Expectations
  • Start date
G

Gordy w/Hi Expectations

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.
 
D

Don Guillett

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.
 
G

Gordy w/Hi Expectations

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.
 
B

Barb Reinhardt

Don,

I was unable to select rows to delete while recording a macro (after using
AutoFilter). I had to code that into a macro. Unfortunately, I don't have
the specifics here to refer to.

Barb
 
D

Don Guillett

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
 
G

Gordy w/Hi Expectations

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.
 
E

exceluserforeman

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
[email protected]

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
 
G

Gordy w/Hi Expectations

range(e,e.specialcells(xlLastCell).select
range(e,e.offset(intRows,0).select

Couldn't run it because of these two formulas.
 
Top