Basic search function

[dubseven]

Hi All -

I have a spreadsheet with 2 sheets.

Sheet 1 contains various columns, one for part numbers, one for prices, one fordescription, etc. and two columns of cells that are called "stage 1" (call this column A) and "stage 2" (Column B). The cells in last two either have a yes or a no in them as a value.

For Sheet 2, I want it to search through Sheet 1, and have it return data from rows that are marked "yes" as "Stage 1".

What is the best way to do this?

Thank you!

 

[hiker95]

Re: Need help with a basic search function

dubseven,

It would help if we could see your actual raw data workbook/worksheets, Sheet 1, and, Sheet 2, and, on another worksheet Sheet 2results what the results (manually formatted by you) should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Fluff]

Re: Need help with a basic search function

How about

Sub FiltrCpy()
   With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1").AutoFilter 1, "Yes"
      .UsedRange.Offset(1).SpecialCells(xlVisible).Copy Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilterMode = False
   End With
End Sub

Change sheet names in red to suit.

 

[dubseven]

Re: Need help with a basic search function

dubseven,

It would help if we could see your actual raw data workbook/worksheets, Sheet 1, and, Sheet 2, and, on another worksheet Sheet 2results what the results (manually formatted by you) should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

https://drive.google.com/open?id=13sk8D-zWvRZtwQa-tbssBLLoNVqwJkFK

On the Stage1 sheet, I want it to populate all the data from the Parts List that have Stage 1 listed as YES in column H

Thank you all for the help!

 

[Fluff]

Re: Need help with a basic search function

Having seen your file, try

Sub FiltrCpy()
   With Sheets("Parts List")
      If .AutoFilterMode Then .AutoFilterMode = False
      .UsedRange.AutoFilter 8, "Yes"
      Intersect(.UsedRange.Offset(1).SpecialCells(xlVisible), Range("A:A,C:F")).Copy Sheets("Stage 1").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilterMode = False
   End With
End Sub

 

[dubseven]

Re: Need help with a basic search function

Apologies for the rookie questions - never used any VB - how do I use that code?

 

[dubseven]

Re: Need help with a basic search function

Anyone?

 

[hiker95]

Re: Need help with a basic search function

Apologies for the rookie questions - never used any VB - how do I use that code?

dubseven,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the macro code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[dubseven]

Re: Need help with a basic search function

Got it.

Assume this has to be run every time you want to update the data? Any way to use a formula so it auto-updates? Will be making frequent changes.

Thank you all again so much!

 

[Fluff]

Re: Need help with a basic search function

I don't know how to do it with a formula, but this will copy the data over whenever you enter yes in col H

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 8 Then Exit Sub
   If LCase(Target) = "yes" Then
      Intersect(Target.EntireRow, Range("A:A,C:F")).Copy Sheets("Stage 1").Range("A" & Rows.Count).End(xlUp).Offset(1)
   End If

End Sub

This needs to go in the sheet module, rather than a standard module.
Right click on the "Parts List" tab, select view code, paste the code into the window that opens up.