Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Data Filtering

  Asked By: Erica    Date: Aug 16    Category: MS Office    Views: 796
  

I am trying to take a bunch of data (each entry listed on seperate rows) and
trying to filter the data. What I am trying to do is to take some data, and
evaluate the data in column "G" of it. If the text in column G matches one of
several cases, I want to copy that ENTIRE for from the 1st spreadsheet to
another worksheet.

I want to take all the unique data entries that matches the case and have them
show up in a fresh spreadsheet.

I wrote the below code, and am having trouble actually getting it to work. Any
suggestions/help/advice?


-------------------------------------------
Sub VinayMacro()
'
' Macro3 Macro
' Macro recorded 11/3/2006 by TEA

Dim currentError As String
Dim nstart As Integer
nstart = 2
Dim row2 As Integer

LastCellsWithData 'runs function below


n = nstart
row2 = 3

While n < LastRowWithData

Worksheets("ww0207").Activate
MsgBox Range(7, 3).Value

currentError = Range("G" & CStr(n)).Value '**********THIS IS WHERE I AM
HAVING ONE PROBLEM**********
If currentError = "Exh. pressure is abnormal (MS2)" Or "Vapor purge timeout"
Then
' Worksheets("ww0207").Activate
Rows(n).Select
'ActiveCell.Rows(n).EntireRow.Select
Selection.Copy
Worksheets("Sheet2").Activate
'Range(2, 1).Select
ActiveCell.Rows(row2).Select
row2 = row2 + 1
MsgBox "HellO"
'ActiveWindow.SmallScroll Down:=-21
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste


Worksheets("ww0207").Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveWindow.SmallScroll Down:=-21
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste

Else
n = n + 1
End If
Wend




'MsgBox "all done"


End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Scarlett Hughes     Answered On: Aug 16

I am trying to take a bunch of data  (each entry  listed on seperate rows) and
trying to filter  the data. What I am trying to do is to take some data, and
evaluate the data in column  "G" of it. If the text  in column G matches one of
several cases, I want to copy  that ENTIRE for from the 1st spreadsheet  to
another worksheet.

I want to take all the unique  data entries  that matches the case  and have them
show up in a fresh spreadsheet.

I wrote the below code, and am having trouble  actually getting it to work. Any
suggestions/help/advice?

Thanks,

Vinay
-------------------------------------------
Sub VinayMacro()
'
' Macro3 Macro
' Macro recorded 11/3/2006 by TEA

Dim currentError As String
Dim nstart As Integer
nstart = 2
Dim row2 As Integer

LastCellsWithData 'runs function  below


n = nstart
row2 = 3

While n < LastRowWithData

Worksheets("ww0207").Activate
MsgBox Range(7, 3).Value

currentError = Range("G" & CStr(n)).Value '**********THIS IS WHERE I AM
HAVING ONE PROBLEM**********
If currentError = "Exh. pressure is abnormal (MS2)" Or "Vapor purge timeout"
Then
' Worksheets("ww0207").Activate
Rows(n).Select
'ActiveCell.Rows(n).EntireRow.Select
Selection.Copy
Worksheets("Sheet2").Activate
'Range(2, 1).Select
ActiveCell.Rows(row2).Select
row2 = row2 + 1
MsgBox "HellO"
'ActiveWindow.SmallScroll Down:=-21
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste


Worksheets("ww0207").Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveWindow.SmallScroll Down:=-21
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
ActiveSheet.Paste

Else
n = n + 1
End If
Wend




'MsgBox "all done"


End Sub

 
Answer #2    Answered By: Marina Smith     Answered On: Aug 16

Autofilter is perfect for this. Try this code.

Sub Macro1()
Range("G1").AutoFilter Field:=1, Criteria1:="=Exh. pressure is
abnormal (MS2)", Operator:=xlOr _
, Criteria2:="=Vapor purge timeout"
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
End Sub

 
Didn't find what you were looking for? Find more on Data Filtering Or get search suggestion and latest updates.




Tagged: