Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default Copy filtered data to sheet 2

The filter works & it goes to the available cell in sheet2 but doesn't paste
the filtered data.

Any ideas?


'Option Explicit
Function FilterAndCopy(rng As Range, Choice As String)

Dim FiltRng As Range
Worksheets("sheet2").Select
Dim a As Integer
a = 0
Do
a = a + 1
Loop Until ActiveCell.Offset(a, 0) = ""
ActiveCell.Offset(a, 0).Activate
ActiveCell = FiltRng


rng.AutoFilter Field:=5, Criteria1:=Choice
On Error Resume Next
Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow
On Error GoTo 0


Worksheets("Sheet2").Select
FiltRng.Copy Worksheets("Sheet2").ActiveCell
Range("A1").Select
Set FiltRng = Nothing
End Function


Other code on the form button is
Private Sub UserForm_Click()
Option Explicit

Private Sub CommandButton1_Click()
Dim rng As Range

'Set Error Handling
On Error GoTo ws_exit:
Application.EnableEvents = False
'Set Range
Set rng = ActiveSheet.UsedRange
'Cancel if no value entered in textbox
If TextBox1.Value = "" Then GoTo ws_exit:
'Call function Filterandcopy
FilterAndCopy rng, TextBox1.Value
rng.AutoFilter
'Exit sub
ws_exit:
Set rng = Nothing
Application.EnableEvents = True
Unload Me
End Sub


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default Copy filtered data to sheet 2

Hi,

This code below copies an autofiltered list to another sheet:
---
Set r = ws.Range("A1").CurrentRegion
With r
.AutoFilter 13, "NZzzM", xlAnd
Set rv = .Offset(1).SpecialCells(xlCellTypeVisible)
End With
rv.Copy ws2.Range("A1")
---
Pascal Baro
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy Paste from Class Sheet to Filtered List on Combined Sheet [email protected] Excel Programming 6 September 16th 08 04:30 PM
How do you copy filtered data to a new sheet? Kerry B Excel Discussion (Misc queries) 3 July 31st 07 08:48 PM
Copy filtered data Secret Squirrel Excel Discussion (Misc queries) 3 March 28th 07 03:05 AM
How to copy just the filtered data? ljCharlie Excel Discussion (Misc queries) 3 February 16th 07 07:38 PM
Copy filtered data Hans Knudsen Excel Discussion (Misc queries) 3 November 25th 05 02:29 PM


All times are GMT +1. The time now is 06:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"