How to set up a "ticked" rows

  • Thread starter Thread starter Beemer
  • Start date Start date
B

Beemer

I am an Excel newbie and want to select say 100 rows at various positions
down a 5000 row table then copy these to a new sheet. Rather than copy and
paste each one I would like to set up a "tick" cell column in each row so
that somehow I can then automatically select all the ticked rows then do a
copy and paste.

How do I do this?

regards,

Beemer
 
Insert a column at A and set the font to Marlett.

Put an a in each row of A to copy then add this code and run it


Sub CopyData()
Dim rng As Range
Dim lastrow As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng = .Range("A1").Resize(lastrow)
rng.AutoFilter Field:=1, Criteria1:="a"
Set rng = rng.SpecialCells(xlCellTypeVisible)
rng.EntireRow.Copy Worksheets("Sheet2").Range("A1")
rng.AutoFilter
End With
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Try something like this:

Example using data in A1:Z100, with Row_1 containing headings.
1)Insert a column in front of the data (so the data is now in B1:AA100)
2)For each row you want to copy, put an "X" in Col_A.
3)From the Excel Main Menu: <data><filter><autofilter>

4)Click the dropdown arrow in A1 and select NON-BLANK
.....now only the headings and the rows marked with an X are visible

5)Select from B1 through the last cell to be copied
.....That will select only the visible cells, ignoring the hidden ones.

6)Ctrl+C.....to copy the those cells
7)Switch to the destination for those cells

8)Ctrl+V.....to paste
or
8)<edit><paste special>Check: Values.....to paste only the values

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
I am an Excel newbie and want to select say 100 rows at various positions
down a 5000 row table then copy these to a new sheet. Rather than copy
and paste each one I would like to set up a "tick" cell column in each row
so that somehow I can then automatically select all the ticked rows then do
a copy and paste.
Click to expand...

As long as you are going to have to "tick" each row anyway, why don't you
simply select the cells directly by pressing the Control Key while clicking
each row header number (you can also Control-Click-Drag at any time to
select multiple contiguous rows at any point) and then, once you have
selected all of the rows, simply Edit/Copy (or press Ctrl+C), switch to the
new sheet and Edit/Paste (or press Ctrl+V) to complete the copy operation?

Rick
 
See inline text correction below....
As long as you are going to have to "tick" each row anyway, why don't you
simply select the cells directly by pressing the Control Key while
clicking
Click to expand...

..... simply selects the ROWS directly....

Rick
 
Bob,

Thanks for this however I'm not into macros yet so I'll save this for the
future.

Beemer
 
Ron,

I liked your solution.

thanks,

Beemer
Ron Coderre said:
Try something like this:

Example using data in A1:Z100, with Row_1 containing headings.
1)Insert a column in front of the data (so the data is now in B1:AA100)
2)For each row you want to copy, put an "X" in Col_A.
3)From the Excel Main Menu: <data><filter><autofilter>

4)Click the dropdown arrow in A1 and select NON-BLANK
....now only the headings and the rows marked with an X are visible

5)Select from B1 through the last cell to be copied
....That will select only the visible cells, ignoring the hidden ones.

6)Ctrl+C.....to copy the those cells
7)Switch to the destination for those cells

8)Ctrl+V.....to paste
or
8)<edit><paste special>Check: Values.....to paste only the values

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
Click to expand...
 
Rick,

You are correct in that selecting directly is the simplest but in the past I
have always hit the wrong key at the wrong time and lost many minutes of
editing. This is worse when I am switching back and forth from Excel to
the web to gather more information about each row.

regards,

Beemer
 
You are correct in that selecting directly is the simplest but in the past
I have always hit the wrong key at the wrong time and lost many minutes of
editing. This is worse when I am switching back and forth from Excel to
the web to gather more information about each row.
Click to expand...

Can't help you with "hitting the wrong key" (although I would think once you
started selecting rows, one hand would be on the Control Key and the other
on the mouse button and remain that way throughout the selection process);
however, the key to not screwing up when switching windows is to reselect
Excel by clicking the Taskbar icon or its Title Bar... and no where else.

Rick
 
Back
Top