AutoFilter

C

Cissy

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
 
D

dok112

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
 
D

dok112

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
 
D

dok112

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
 
D

dok112

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
 
D

dok112

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
 
D

dok112

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
 
C

Cissy

This worked great. Thanks!

dok112 said:
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



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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using IF statement and managing toolbars 2
cannot insert row in vba 1
MACRO Effeciency 3
filtering two pieces of data 3
help with dates in the past 5
How to Activate Filter in Protected Sheet 2
another filtering question 1
setting more than one filter 1

Top