Program to pull selected rows from other worksheets

Altum681

New Member
Joined
Sep 10, 2014
Messages
12
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
DateDescriptionCard MemberAmountCategorySubCategoryTripReceiptPayerNotes


<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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
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

New Member
Joined
Sep 10, 2014
Messages
12
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

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,745
Messages
5,626,624
Members
416,195
Latest member
tonmcg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top