Program to pull selected rows from other worksheets

[Altum681]

http://www.mrexcel.com/forum/excel-...ected-rows-multiple-sheets-summary-sheet.html

The above post is related, but to simplistic. I have a mother of a workbook...with about 30 sheets. Each sheet is formatted similarly with headers of

Date

Description

Card Member

Amount

Category

SubCategory

Trip

Receipt

Payer

Notes

<tbody>
</tbody>

It is based off of an AmEx bill for many years. I would like a program that would pull all the lines that start with a given Description (though many have other letters following, ie Verizon 7 is the same as Verizon 8) and pull them into a final worksheet. Ideally I would like this flexible enough that I could change the descriptor easily. The program answer attached does something similar, but for only 4 worksheets, which means in my case I would have to have 30 some segments, per worksheet, and change the descriptor many times to edit. A variable may be the way around that... If it helps all the worksheets are labelled similarly with their title being Month.18.Last 2 digits of year (ie 2.18.14 for Feb 2014).

Any thoughts would be great!

 

[NeonRedSharpie]

Sub returnRows()

    Dim searchTerm As String
    Dim nextRow As Long
    
    nextRow = 2
    
    With ThisWorkbook.Sheets(1)
        .Range("A2:J" & .Cells(Rows.Count, "A").End(xlUp).Row).Clear
    End With
    
tryagain:
    searchTerm = InputBox("Enter your search term...", "Search Term", "Input Here")
    
    If searchTerm = "Input Here" Then GoTo tryagain:
    
    For x = 2 To ThisWorkbook.Sheets.Count
    
        For y = 2 To Sheets(x).Cells(Rows.Count, "B").End(xlUp).Row
            If InStr(1, Sheets(x).Cells(y, 2), searchTerm, vbTextCompare) > 0 Then
                Sheets(1).Cells(nextRow, 2).EntireRow.Value = Sheets(x).Cells(y, 2).EntireRow.Value
                nextRow = nextRow + 1
            End If
            
        
        Next y
    
    Next x


End Sub

This assumes that sheet(1) is your summary sheet and you're totally ok with that being deleted. It them flips through the remaining sheets (up to however many) and returns the ENTIRE row into sheet(1). It's based on an input that is prompted in the beginning.

 

[Altum681]

I am new to Excel programming. When I copy and paste that into my macro editor nothing happens. What edits do I need to make to it? Also, when you discuss deleting, I need the original entries to stay the same, more like a copy and paste, as opposed to a cut and paste. Thanks.

 

[NeonRedSharpie]

Right, they will stay the same. I'm just using your sheet 1 as a blank slate. Everytime the macro is run, it will clear out sheet 1 and plaste the values there. If you don't want that we can change that.

You need to copy it into your macro editor and hit F5. Also, be sure to have a saved off copy in case the macro does something wonky.