Help with Code

[Soniya]

Hi All,

I am having the following code which stpos after copying
data from the first instance of the sheet. How cud i fix
this problem?


Sub CurMonth()
Sheets("CasCrd").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Interface").Select

For Each Sh In Sheets
Sh.Select
If Mid(Sh.Name, 2, 2) = Right(Sheets("Interface").[C2],
2) Then

Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="N"
Selection.End(xlDown).Select
Range(Selection, Range("AP1").Offset(1, 0)).Copy _
Destination:=Sheets("CasCrd").Range("A65536").End
(xlUp).Offset(1, 0)
Sh.AutoFilterMode = False
Range("A1").Select
End If
Next


End Sub



TIA
Soniya

 

[Dave Peterson]

How about something like this:

Option Explicit
Sub CurMonth()
Dim sh As Worksheet

Worksheets("CasCrd").Rows("2:65536").Clear

For Each sh In Worksheets
With sh
If LCase(Mid(.Name, 2, 2)) _
= LCase(Right(Worksheets("Interface").Range("c2").Value, 2)) Then
.Range("A1").CurrentRegion.AutoFilter Field:=4, Criteria1:="N"
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Count = 1 Then
'do nothing--no N's found
Else
.Offset(1, 0).Resize(.Rows.Count - 1).Cells _
.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("CasCrd").Range("A65536") _
.End(xlUp).Offset(1, 0)
End If
End With
.AutoFilterMode = False
End If
End With
Next sh

End Sub

Hi All,

I am having the following code which stpos after copying
data from the first instance of the sheet. How cud i fix
this problem?

Sub CurMonth()
Sheets("CasCrd").Select
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Interface").Select

For Each Sh In Sheets
Sh.Select
If Mid(Sh.Name, 2, 2) = Right(Sheets("Interface").[C2],
2) Then

Range("A1").Select
Selection.AutoFilter Field:=4, Criteria1:="N"
Selection.End(xlDown).Select
Range(Selection, Range("AP1").Offset(1, 0)).Copy _
Destination:=Sheets("CasCrd").Range("A65536").End
(xlUp).Offset(1, 0)
Sh.AutoFilterMode = False
Range("A1").Select
End If
Next

End Sub

TIA
Soniya