Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
for Tom Ogilvy Please
Tom Hi,
I have copied your CopyFilter sub from the Contextures site but want to copy to a new worksheet a "specific range" rather than the whole of the filtered area. I have amended your code as shown below but cannot get the range parameters right. Your assistance please. Sub CopyFilter2() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range Dim rng3 As Long Application.ScreenUpdating = False Range("A5").AutoFilter Field:=1, Criteria1:="01" 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").Range("A2:L25").ClearContents Set rng = ActiveSheet.AutoFilter.Range ' I have added this to count visible rows rng3 = rng.Columns(4).SpecialCells(xlCellTypeVisible).Cou nt - 1 ' Range I want to copy starts in the first row column 4 of filtered ' range to the last cell in say Column 10 of the filtered range Range(Cells(rng.Offset(1, 4), rng.Columns(4)), _ Cells(rng3, rng.Columns(10))).Copy Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlValues End If On Error Resume Next ActiveSheet.ShowAllData Application.ScreenUpdating = True End Sub Many thanks in davance/sgl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Tom Ogilvy | Excel Programming | |||
Tom Ogilvy again | Excel Programming | |||
Tom Ogilvy | Excel Programming | |||
Tom Ogilvy | Excel Programming | |||
tom ogilvy? somebody? | Excel Programming |