selectively copy rows from multiple sheets to another sheet

[thedeacon]

Apologies if this has been covered already - my search for prior posts did not turn up an exact solution.

Excel 2002, SP3
VB experience = very minimal

I have a worksheet with 12 tabs - one for each month of the year. On each month's tab, I have a TRANSACTION column, a DATE column, an AMOUNT column, and a CATEGORY column. I copy my credit card transactions to the month tabs throughout the year as they post online. I want to create a 13th tab that lists all of the rows from all of the tabs that have a specific value in the CATEGORY column. For example, I want the 13th tab to list all of the rows from all of the 12 month tabs that have a category of "gasoline". Also, I would like the data on the 13th tab to automatically update when new data is entered on any of the month tabs throughout the year.

Thanks in advance for any help with this.

 

[onlyadrafter]

Hello and welcome,

this code will work for existing data

Sub COPY_TO_CATEGORY()
    Sheets(13).Range("A2:IV65536").ClearContents
    For MY_SHEETS = 1 To 12
        With Sheets(MY_SHEETS)
            .Rows(1).AutoFilter
            .Rows(1).AutoFilter Field:=4, Criteria1:="gasoline"
            If .Range("D" & Rows.Count).End(xlUp).Row > 1 Then
                .Range("A2:D" & .Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
                    Sheets(13).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
            .Rows(1).AutoFilter
        End With
    Next MY_SHEETS
End Sub

This starts by clearing the 13th TAB

Have assumed that Row 1 are headers, data starts in row 2 and COL D is where gasoline etc is. ALso assumed the 13th sheet is at the end of the TAB list.

How is the data entered in each sheet, this will determine where to place the code.

 

[thedeacon]

Thank you for your reply.

A little more info that I did not mention:
- data is manually entered into the individual month tabs monthly
- the month tabs are named: JAN, FEB, MAR....DEC and are not the first 12 tabs in the workbook
- the tab where I want the info to go is named SUMMARY and is not the 13th tab in the workbook (and it is not the last tab either)

All the rest of your assumptions (header rows, etc) are valid.

Again, thanks for your help!

 

[onlyadrafter]

Hello,

try

Sub COPY_TO_CATEGORY()
    Sheets(13).Range("A2:IV65536").ClearContents
    For MY_SHEETS = 1 To 12
        With Sheets(MY_SHEETS)
            .Rows(1).AutoFilter
            .Rows(1).AutoFilter Field:=4, Criteria1:="gasoline"
            If .Range("D" & Rows.Count).End(xlUp).Row > 1 Then
                .Range("A2:D" & .Range("D" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
                    Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
            .Rows(1).AutoFilter
        End With
    Next MY_SHEETS
End Sub

are your MONTH tab sheets always in the same place, if so then change

For MY_SHEETS = 1 To 12

to the relevant sheet tab numbers. If they can move about post back and I will supply another solution.

 

[thedeacon]

Thanks! That seems to work just fine (with a few minor tweaks). Thank you so much for your help.