Use the following Code...this will input it for you.

Sub ReportBatch()

Dim Message2, Title2
Dim MyValue2 As String

Message2 = "Enter the Batch Number for Report"
Title2 = "Batch Number for Report"
MyValue2 = InputBox(Message2, Title2)

Dim ValA As Variant
[ValA] = MyValue2

Worksheets("Main").Activate
Application.Goto reference:="R2C1"
Selection.AutoFilter
Selection.AutoFilter field:=2, Criteria1:="" & [ValA] & ""
Range("A2:I15270").Select
Selection.Copy
Sheets("Batch Report").Activate
Application.Goto reference:="R2C1"
ActiveSheet.Paste
Application.Goto reference:="R2C1"

Worksheets("Main").AutoFilterMode = False
Application.Goto reference:="R2C1"


End Sub




Cissy Wrote:
Hi,

I'm using Excel '03. Thanks for your help. I have the following
macro
designed to take selected info from one sheet and paste it into
another. I
want the macro to prompt me for the criteria to be used in the
AutoFilter,
grab those rows, clear the autofilter and paste those rows into my
"report"
sheet. Can you help? Thank you.

Sub ReportBatch()

Dim Message2, Title2
Dim MyValue2 As String

Message2 = "Enter the Batch Number for Report"
Title2 = "Batch Number for Report"
MyValue2 = InputBox(Message2, Title2)

Worksheets("Main").Activate
Application.Goto reference:="R2C1"
Selection.AutoFilter
Selection.AutoFilter field:=2, Criteria1:="MyValue2"
Range("A2:I15270").Select
Selection.Copy
Sheets("Batch Report").Activate
Application.Goto reference:="R2C1"
ActiveSheet.Paste
Application.Goto reference:="R2C1"

Worksheets("Main").AutoFilterMode = False
Application.Goto reference:="R2C1"


End Sub


--
dok112
------------------------------------------------------------------------
dok112's Profile: http://www.excelforum.com/member.php...o&userid=10581
View this thread: http://www.excelforum.com/showthread...hreadid=532000