I am using Autofilter programmatically to filter a database spreadshee
and then using a modified CopyFilter routine by Tom Ogilvy to copy th
rows to a TempSheet which is then copied and transposed to ViewShee
for user viewing.

The problem is not all of the rows are copied from TempSheet t
ViewSheet. I realize I may not need TempSheet to act as an intermediar
- but I'm not sure how to avoid it ... yet.

Here is Toms original code...

Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Else
Worksheets("Sheet2").Cells.Clear
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
Destination:=Worksheets("Sheet2").Range("A1")
End If
ActiveSheet.ShowAllData

End Sub


and my modified code...

Sub CopyFilter()
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
Worksheets("TempSheet").Cells.Clear
Worksheets("ViewSheet").Cells.Clear
If rng2 Is Nothing Then
'MsgBox "No data found"
Else
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(0, 0).Resize(rng.Rows.Count).Copy _
Destination:=Worksheets("TempSheet").Range("A1")
End If
Worksheets("TempSheet").Select
Selection.Copy
Sheets("ViewSheet").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=True
Worksheets("Total_Hardware").Select
ActiveSheet.ShowAllData
Worksheets("ViewSheet").Select
Cells(22, 1).Value = "Back To"
Cells(22, 1).Font.Bold = True
Cells(22, 1).HorizontalAlignment = xlCenter
LinkText = "Summary!A1"
ActiveSheet.Hyperlinks.Add Anchor:=Cells(22, 2), _
Address:="", _
SubAddress:=LinkText
TextToDisplay:="Summary"
Cells(22, 2).Font.Bold = True
Cells(22, 2).HorizontalAlignment = xlCenter
End Sub

Any help is appreciated ! Thanks

--
Message posted from http://www.ExcelForum.com