I'm trying to create a macro in Excel 2002 which will select certain rows of
a worksheet using the Autofilter capability. The (non-contiguous) rows will
then be copied to another worksheet within the file. The number of rows
selected will range from zero on up. Since the number of rows will be
different each time, and I don't know how to get Excel to deal with that
variability, I want to have the macro pause to let the user select the rows
to be copied, then continue once the selection is made. (Assume the user
will have only minimal skills with Excel.) How do I pause the macro, let the
user can make his/her selections, then resume the macro? I have VERY LIMITED
VBA programming skills.

You probably can do this using special cells
Look in the vba help index for SPECIALCELLS

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RBLampert" wrote in message
...
I'm trying to create a macro in Excel 2002 which will select certain rows
of
a worksheet using the Autofilter capability. The (non-contiguous) rows
will
then be copied to another worksheet within the file. The number of rows
selected will range from zero on up. Since the number of rows will be
different each time, and I don't know how to get Excel to deal with that
variability, I want to have the macro pause to let the user select the
rows
to be copied, then continue once the selection is made. (Assume the user
will have only minimal skills with Excel.) How do I pause the macro, let
the
user can make his/her selections, then resume the macro? I have VERY
LIMITED
VBA programming skills.

You would have to use Application.InputBox with Type:=8.

MyVariable = Application.InputBox("Select Range to Copy.", "Select", Type:=8)
Range(MyVariable).Copy
Destination:=

"RBLampert" wrote:

I'm trying to create a macro in Excel 2002 which will select certain rows of
a worksheet using the Autofilter capability. The (non-contiguous) rows will
then be copied to another worksheet within the file. The number of rows
selected will range from zero on up. Since the number of rows will be
different each time, and I don't know how to get Excel to deal with that
variability, I want to have the macro pause to let the user select the rows
to be copied, then continue once the selection is made. (Assume the user
will have only minimal skills with Excel.) How do I pause the macro, let the
user can make his/her selections, then resume the macro? I have VERY LIMITED
VBA programming skills.

No need. After autofiltering, just copy the visible rows using specialcells

Set rngToCopy = rngFiltered.SpecialCells(xlCellTypeVisible).Entire Row

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"RBLampert" wrote in message
...
I'm trying to create a macro in Excel 2002 which will select certain rows
of
a worksheet using the Autofilter capability. The (non-contiguous) rows
will
then be copied to another worksheet within the file. The number of rows
selected will range from zero on up. Since the number of rows will be
different each time, and I don't know how to get Excel to deal with that
variability, I want to have the macro pause to let the user select the
rows
to be copied, then continue once the selection is made. (Assume the user
will have only minimal skills with Excel.) How do I pause the macro, let
the
user can make his/her selections, then resume the macro? I have VERY
LIMITED
VBA programming skills.

Bob, this idea LOOKS like it'll be the answer to a lot of needs, but I can't
make it work. The problem is my EXTREMELY limited programming skills. I
added a Dim statement immediately before this line making rngToCopy an Object
(see code below) but when I run the macro I get an "object required" error
(#424) for your code line. Same thing happens if I designate rngToCopy as a
Range. I sometimes get a tooltip that tells me the value of rngToCopy is
"NOTHING."

Here's the macro code:
Sub EMails2()
'
' EMails2 Macro
' Macro recorded 11/12/2007 by Ross B. Lampert
'

'
Selection.AutoFilter
Selection.AutoFilter Field:=19, Criteria1:="<"
Dim rngToCopy As Object
Set rngToCopy = rngFiltered.SpecialCells(xlCellTypeVisible).Entire Row
Sheets("E-Mails").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

What am I doing wrong?

Thanks.

"Bob Phillips" wrote:

No need. After autofiltering, just copy the visible rows using specialcells

Set rngToCopy = rngFiltered.SpecialCells(xlCellTypeVisible).Entire Row

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"RBLampert" wrote in message
...
I'm trying to create a macro in Excel 2002 which will select certain rows
of
a worksheet using the Autofilter capability. The (non-contiguous) rows
will
then be copied to another worksheet within the file. The number of rows
selected will range from zero on up. Since the number of rows will be
different each time, and I don't know how to get Excel to deal with that
variability, I want to have the macro pause to let the user select the
rows
to be copied, then continue once the selection is made. (Assume the user
will have only minimal skills with Excel.) How do I pause the macro, let
the
user can make his/her selections, then resume the macro? I have VERY
LIMITED
VBA programming skills.

Untested but try

sub doit()
Selection.AutoFilter Field:=19, Criteria1:="<"
selection.SpecialCells(xlCellTypeVisible).copy
Sheets("E-Mails").Range("A2")
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RBLampert" wrote in message
...
Bob, this idea LOOKS like it'll be the answer to a lot of needs, but I
can't
make it work. The problem is my EXTREMELY limited programming skills. I
added a Dim statement immediately before this line making rngToCopy an
Object
(see code below) but when I run the macro I get an "object required" error
(#424) for your code line. Same thing happens if I designate rngToCopy as
a
Range. I sometimes get a tooltip that tells me the value of rngToCopy is
"NOTHING."

Here's the macro code:
Sub EMails2()
'
' EMails2 Macro
' Macro recorded 11/12/2007 by Ross B. Lampert
'

'
Selection.AutoFilter
Selection.AutoFilter Field:=19, Criteria1:="<"
Dim rngToCopy As Object
Set rngToCopy = rngFiltered.SpecialCells(xlCellTypeVisible).Entire Row
Sheets("E-Mails").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

What am I doing wrong?

Thanks.

"Bob Phillips" wrote:

No need. After autofiltering, just copy the visible rows using
specialcells

Set rngToCopy = rngFiltered.SpecialCells(xlCellTypeVisible).Entire Row

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"RBLampert" wrote in message
...
I'm trying to create a macro in Excel 2002 which will select certain
rows
of
a worksheet using the Autofilter capability. The (non-contiguous) rows
will
then be copied to another worksheet within the file. The number of rows
selected will range from zero on up. Since the number of rows will be
different each time, and I don't know how to get Excel to deal with
that
variability, I want to have the macro pause to let the user select the
rows
to be copied, then continue once the selection is made. (Assume the
user
will have only minimal skills with Excel.) How do I pause the macro,
let
the
user can make his/her selections, then resume the macro? I have VERY
LIMITED
VBA programming skills.

copy _
or bring up the next line so it is ONE line

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Untested but try

sub doit()
Selection.AutoFilter Field:=19, Criteria1:="<"
selection.SpecialCells(xlCellTypeVisible).copy
Sheets("E-Mails").Range("A2")
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RBLampert" wrote in message
...
Bob, this idea LOOKS like it'll be the answer to a lot of needs, but I
can't
make it work. The problem is my EXTREMELY limited programming skills. I
added a Dim statement immediately before this line making rngToCopy an
Object
(see code below) but when I run the macro I get an "object required"
error
(#424) for your code line. Same thing happens if I designate rngToCopy as
a
Range. I sometimes get a tooltip that tells me the value of rngToCopy is
"NOTHING."

Here's the macro code:
Sub EMails2()
'
' EMails2 Macro
' Macro recorded 11/12/2007 by Ross B. Lampert
'

'
Selection.AutoFilter
Selection.AutoFilter Field:=19, Criteria1:="<"
Dim rngToCopy As Object
Set rngToCopy = rngFiltered.SpecialCells(xlCellTypeVisible).Entire Row
Sheets("E-Mails").Select
Range("A2").Select
ActiveSheet.Paste
End Sub

What am I doing wrong?

Thanks.

"Bob Phillips" wrote:

No need. After autofiltering, just copy the visible rows using
specialcells

Set rngToCopy = rngFiltered.SpecialCells(xlCellTypeVisible).Entire Row

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"RBLampert" wrote in message
...
I'm trying to create a macro in Excel 2002 which will select certain
rows
of
a worksheet using the Autofilter capability. The (non-contiguous)
rows
will
then be copied to another worksheet within the file. The number of
rows
selected will range from zero on up. Since the number of rows will be
different each time, and I don't know how to get Excel to deal with
that
variability, I want to have the macro pause to let the user select the
rows
to be copied, then continue once the selection is made. (Assume the
user
will have only minimal skills with Excel.) How do I pause the macro,
let
the
user can make his/her selections, then resume the macro? I have VERY
LIMITED
VBA programming skills.

This suggestion (with later correction) works too well. Instead of copying
only the rows selected by the autofilter, it copies them AND every possible
blank row below them, down to row #65,000+. Then I get a "Paste method of
worksheet class failed" error message.

"Don Guillett" wrote:

Untested but try

sub doit()
Selection.AutoFilter Field:=19, Criteria1:="<"
selection.SpecialCells(xlCellTypeVisible).copy
Sheets("E-Mails").Range("A2")
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

What was selected when you ran the code?

Try just selecting the range you want filtered--not the entire column.

RBLampert wrote:

This suggestion (with later correction) works too well. Instead of copying
only the rows selected by the autofilter, it copies them AND every possible
blank row below them, down to row #65,000+. Then I get a "Paste method of
worksheet class failed" error message.

"Don Guillett" wrote:

Untested but try

sub doit()
Selection.AutoFilter Field:=19, Criteria1:="<"
selection.SpecialCells(xlCellTypeVisible).copy
Sheets("E-Mails").Range("A2")
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software



--

Dave Peterson