Searching Excel Worksheet

[skirk112]

Hi,

I am quite new to using excel, what I am trying to do is:

I have 12 different sheets named April-06-sales through to march-07-sales

What I need is to be able to search all of the sheets and check if the invoiced paid column is blank and if it is copy that row into a new sheet. All of the sheets have a different number of rows.

Please can any point me into the right direction on how to achieve this

Thank you for your assistance!

 

[chicagodiceman]

Welcome to the board.

This should do what you're looking for. I'm sure there are better ways to code and you'll get response from others but assuming your data goes from columns A to E and column E contains the paid information. This macro filters on column E for blanks. It then copies the visible cells and moves them to sheet "total". Make sure "total" is the last sheet in the workbook. Modify your references as needed.

It first selects A2 in totals so that your data begins below your headings. If no headings, change to A1.

Sub Macro3()
Sheets("totals").Activate
Range("a2").Select
    FirstWS = 1
    LastWS = Worksheets.Count - 1
For i = FirstWS To LastWS
Worksheets(i).Select

Range("A65536").End(xlUp).Offset(1, 0).Select
LASTROW = ActiveCell.Row - 1
    Selection.AutoFilter
    Selection.AutoFilter Field:=5, Criteria1:="="
    Range("A2:D" & LASTROW).Select
    Selection.Copy
    Sheets("totals").Select
    ActiveSheet.Paste
Range("A65536").End(xlUp).Offset(1, 0).Select
Sheets(i).Activate
Selection.AutoFilter
Range("A2").Select
Next i
End Sub

 

[skirk112]

Thank you for your reply,

I have tried the code that you supplied and I get 'Autofilter of Range class failed' error.

To give a little more information of how the data is set out

I have 12 columns and the last column is the information regarding invoice paid. The data starts on A5 and the last row is the 'total invoice amounts'

Thank you so much for your time, this is so over my head!

 

[chicagodiceman]

A little bit more information and I or one of the Gurus will be able to help with this.

Are there headers in A5:L5 or does it begin with data?
What is the sheet name that you want to store the copied information to?
Which position is the "summary" sheet in (i.e. first sheet, 13th sheet, etc.)?
Did you do any modification of my macro to reflect how your data is set up?

Let us know and we should be able to come up with a good solution!