J
jln via OfficeKB.com
What my problem is when this code is ran and all data is exclued because of
the auto filter all the data gets copyed. How can i stop this?
Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
With Worksheets("NetPILIQ")
Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:=">0" ' HERE is THE PROBLEM
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 3).End(xlUp)(2)
rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1]C)"
With Worksheets("NetPILIQ")
Set rng6 = .Range(.Cells(5, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
rng6.Offset(0, 3).FormulaR1C1 = "=Sum(RC[-3],RC[-1])"
End Sub
the auto filter all the data gets copyed. How can i stop this?
Sub NetPIlIQ()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
With Worksheets("NetPILIQ")
Range("A5:E65536").Clear
End With
Sheets("TOEPIEXP").Select
With Worksheets("TOEPIEXP")
Set rng = .Range("A1").CurrentRegion
rng.AutoFilter Field:=24, Criteria1:=">0" ' HERE is THE PROBLEM
Set rng2 = .AutoFilter.Range
Set rng2 = rng2.Offset(1, 0).Resize( _
rng2.Rows.Count - 1)
Set rng3 = .Range("B:B,E:E,R:R,X:X,AD:AD").EntireColumn
Set rng1 = Intersect(rng2.EntireRow, rng3)
End With
Set rng4 = Worksheets("NetPILIQ") _
Cells(Rows.Count, 1).End(xlUp)(2)
If rng4.Row < 6 Then
Set rng4 = Worksheets("NetPILIQ").Range("A5")
rng1.Copy rng4
End If
Worksheets("TOEPIEXP").AutoFilterMode = False
Set rng5 = Worksheets("NetPILIQ").Cells(Rows.Count, 3).End(xlUp)(2)
rng5.Resize(1, 4).FormulaR1C1 = "=Sum(R5C:R[-1]C)"
With Worksheets("NetPILIQ")
Set rng6 = .Range(.Cells(5, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
rng6.Offset(0, 3).FormulaR1C1 = "=Sum(RC[-3],RC[-1])"
End Sub