Excel

Filter a sheet and copy results to another sheet

Ease of Use

Easy

Version tested with

xl2000, 2003 

Submitted by:

gibbo1715

Description:

The code will ask you what you would like to search for, it will then filter a column based on what you ask for and then copy the results into another sheet, Basically its a copy paste routine 

Discussion:

If you have a sheet that you need to search, but you only want to search one column then this routine will find each instance of what you are searching for and copy each row to another sheet 

Code:

instructions for use 

			
 'In a Standard Module
 
Option Explicit 
Function FilterAndCopy(rng As Range, Choice As String) 
     
    Dim FiltRng As Range 
     'Clear Contents to show just new search data
    Worksheets("Sheet2").Cells.ClearContents 
     'Set the column to filter (In This Case 1 or A)
     'Change as required
    rng.AutoFilter Field:=1, Criteria1:=Choice 
    On Error Resume Next 
    Set FiltRng = rng.SpecialCells(xlCellTypeVisible).EntireRow 
    On Error GoTo 0 
     
     'Copy Data across to sheet 2
    FiltRng.Copy Worksheets("Sheet2").Range("A1") 
     'Display Data
    Worksheets("Sheet2").Select 
    Range("A1").Select 
    Set FiltRng = Nothing 
End Function 
 
Sub formshow() 
     'Show Search Form
    UserForm1.Show 
End Sub 
 
 '*****************************************************************
 'In a userform
 
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 
 
Private Sub CommandButton2_Click() 
     'Cancel Button
    Unload Me 
End Sub

How to use:

  1. Open Microsoft Excel
  2. Press Alt + F11 to open the Visual Basic Editor (VBE)
  3. Add a new standard module (Top Left)
  4. Copy the In a Standard Module code above into the right pain
  5. Paste code into the right pane
  6. Add a new userform (Top Left)
  7. add two button and a text box to your userform
  8. double click anywhere on the userform
  9. Copy the userform code above into the right pain
  10. Return to excel and add a button
  11. Attach Macro formshow to button
  12. Thats it, it will now search the sheet with the button on and return the results
  13. Results returned to Sheet2 so you must ensure you have a worksheet called Sheet2
 

Test the code:

  1. Enter data onto sheet with button on
  2. Click Button
  3. Userform will now be displayed
  4. Enter search criteria in textbox (Searches Column A)
  5. HIt Search Button
  6. Your results will now be displayed
 

Sample File:

copy and paste using filter.zip 11.1KB 

Approved by mdmackillop


This entry has been viewed 606 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express