Autofilter and If statement

[irisqueiroz]

Hi!


I have one workbook with several worksheets. Each worksheet has a autofilter... i'm interested in one column (G) that has 3 criterias: "n", "e" or "d".

Then i'll make a loop into those worksheets, and copy and paste to an other workbook that's has 3 sheets: "n", "e" or "d".

The problem is that not all sheets has the 3 criterias... some have just "n" and "d", for exemple. I don't know how to write a code to check when I look for a criteria is the line below the autofilter is empty or not.

The offset(1,0).value method doesen't work well...


The code is big but what I need to know is marked.


Sub procedimentos()


Dim i As Long

Dim a As Long 'última linha


Windows("A_PAGAR.xlsm").Activate


For i = 2 To 74


Sheets(i).Select


ActiveSheet.Range("$A$1:$G$500").AutoFilter Field:=7, Criteria1:="e"


If Cells(1, 7).Offset(1, 0) <> 0 Then <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


Range("A2").Select

a = Range("A1048576").End(xlUp).Row

Range(Selection, Selection.End(xlDown)).Select

Range(Cells(2, 1), Cells(a, 7)).Select

Selection.Copy

Windows("procedimento.xlsx").Activate

Sheets(1).Select

Range("A1").Select

Selection.End(xlDown).Select

Selection.End(xlDown).Offset(1, 0).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Windows("A_PAGAR.xlsm").Activate


End If


ActiveSheet.Range("$A$1:$G$500").AutoFilter Field:=7, Criteria1:="n"


If Cells(1, 7).Offset(1, 0) <> 0 Then


Range("A2").Select

a = Range("A1048576").End(xlUp).Row

Range(Selection, Selection.End(xlDown)).Select

Range(Cells(2, 1), Cells(a, 7)).Select

Selection.Copy

Windows("procedimento.xlsx").Activate

Sheets(2).Select

Range("A1").Select

Selection.End(xlDown).Select

Selection.End(xlDown).Offset(1, 0).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Windows("A_PAGAR.xlsm").Activate


End If


ActiveSheet.Range("$A$1:$G$500").AutoFilter Field:=7, Criteria1:="d"


If Cells(1, 7).Offset(1, 0).Value <> 0 Then


Range("A2").Select

a = Range("A1048576").End(xlUp).Row

Range(Selection, Selection.End(xlDown)).Select

Range(Cells(2, 1), Cells(a, 7)).Select

Selection.Copy

Windows("procedimento.xlsx").Activate

Sheets(3).Select

Range("A1").Select

Selection.End(xlDown).Select

Selection.End(xlDown).Offset(1, 0).Select

ActiveSheet.Paste

Application.CutCopyMode = False

Windows("A_PAGAR.xlsm").Activate


End If


Next


End Sub

 

[kaushik03]

Hi irisqueiroz,


In one of the post I have written a macro to take care of similar kind of issue. Please read the post below.


http://chandoo.org/forums/topic/fetching_records_from_another-sheet.


There are some sheets where filtering criteria(employee code) does not exist.

'On Error GoTo errhandler:' is taking care of that part.


Examine the code and workbook and I hope you will be able to adapt the same in your work. In case, you face any challenge please feel free to post back here and please consider uploading your sample workbook as well.


Regards,

Kaushik