Wanted to go through a long list rows and if one is checked or a Y for yes is in it create that row in a new spreadsheet in order of selection

Google Sheets or Excel Macros Question.
I have a list of tables from a database. There are 905 total.
I want to look down the list and either
          1. Select a checkbox in the column
          2. Place a value like a 'Y' in the column
Then have the list of checked values build a new list on a new sheet.

That way I do not have to manually copy every row and move to a new sheet and paste the row...? I am going to be selecting hundreds at a time over and over. It will take me forever unless someone knows of a better way...? This Macro thing sounded pretty good.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached with a code on Module1 and a button called "Search & Create Queries" on Tables Sheet.
If you click that button, the code will prompt you to input a search keyword, the code will then search column C on Tables Sheet for the search keyword and list all the found matching records on a new sheet called Queries.

See if this is what you are trying to achieve.

Sub CreateQueries()
Dim sws As Worksheet, dws As Worksheet
Dim lr As Long, i As Long
Dim strKeyword As String, firstAddress As String
Dim keyRng As Range
Dim x()

Application.ScreenUpdating = False

strKeyword = InputBox("Please enter a keyword to search.", "Search Keyword!")

If strKeyword = "" Then
    MsgBox "You didn't enter a keyword.", vbExclamation
    Exit Sub
End If

Set sws = Sheets("Tables")
On Error Resume Next
Set dws = Sheets("Queries")
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Queries"
End If
dws.Range("A1").Value = UCase(strKeyword)

Set keyRng = sws.Columns(3).Find(what:=strKeyword, after:=sws.Range("C1"), lookat:=xlPart, MatchCase:=False)
If Not keyRng Is Nothing Then
    firstAddress = keyRng.Address
        i = i + 1
        ReDim Preserve x(1 To i)
        x(i) = "Select * From " & keyRng.Value & ";"
        Set keyRng = sws.Columns(3).FindNext(keyRng)
    Loop While Not keyRng Is Nothing And firstAddress <> keyRng.Address
    dws.Range("A2").Resize(i, 1).Value = Application.Transpose(x)
    MsgBox "No record was found for " & strKeyword & ".", vbExclamation
End If
Application.ScreenUpdating = True
End Sub

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Why not upload a sample workbook to show the layout of the raw data you have and mock up the desired output on another sheet?
Only few rows of dummy data would be enough for the sample workbook.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
This is the list of tables on Sheet Tables Second Sheet is the Sample sheet. Any advice appreciated. I assume you just wanted to see the file because you must know how to do this and wanted to just put it together. If that is the case. Thank you.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Where is the file? Did you forget to upload it?
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
For some weird reason the site is not letting me upload the .xlsx file. This has happened more than once. I am going to move to another window. And try again.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's really weird indeed.
You can zip the file and upload it.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
File image that the site will not let me upload. Meets the requirements. Only 27KB and xlsx?
Site will not let me upload anythoughts are appreciated. I do not know how to upload. Maybe if I sent it through personal PM. Meets requirement with 27 KB in size and it is an xlsx file
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try it and see if that works.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I can upload a file without an issue.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Here it is zipped in Windows tool. No upload capability. The file originated in Google Sheets. I downloaded it or exported it with the XLSX file format. I used Windows Zip to get the other file. Not working???
Rob HensonFinance AnalystCommented:
Without the sample file, may I suggest you look at AutoFilter?

In a separate column mark your selections with a Y as suggested. Then select the whole dataset and apply an AutoFilter. On the Data ribbon use the large funnel button to apply the filter. This will put a dropdwon box against all of the headers. In the column where you have put the Y selections click the dropdown and you will see various filter options. At the bottom of the options there will be a list of values with tick boxes; it will probably list:
  1. Select All
  2. Y
  3. Blanks
Change the selection so that only the Y is selected and press OK. Only the rows with Y in that column will now be visible.

Select the whole dataset again and press Ctrl & C to copy to the clipboard.

Select the destination sheet and cell and press Ctrl & V to paste. Only the cells that were visible will have been copied and pasted.

On the original sheet if you now want rid of those rows, with the rows still selected press Ctrl & Minus key and the rows will be deleted.

De-activate the filter by reselecting Select All in the dropdown and the remaining rows will now be visible again.
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Here is what I was trying to figure out in a format that seems to work now. Not sure what happened. I am trying to search through a column list and find any row that contains a specific text in it......in any order....such as the word 'ITEM' then take a specific range of the row and create/generate a new list that I can then copy and past. Not sure if it would be better to produce a formula or if there is a function that will do that in excel? Or even a way to create a prompt that lets the user enter a new record as needed without touching the formula each time?
What I am trying to do with my search term and the recreation of a list I need.MyTableList.xlsx
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
That is super cool. Exactly what I needed. I did not know it would take that much code but it lets me pull all tables with a specific value. I am grateful.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Fletcher! Glad it worked as desired.
Rob HensonFinance AnalystCommented:
Following on from my AutoFilter suggestion, you can just filter the column in which you'd like to find the specific text.

Apply an AutoFilter and use the dropdown in the header cell of the filter column to show filter options. There will be a list of specific values and also an entry box for search for a specific term. You don't need wild cards, just enter the word that you want to find and the list of values below will shrink accordingly. Click OK to show results.

As mentioned earlier, when you copy from a filtered list only the visible cells are copied.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.