Searching Excel Worksheet

skirk112

New Member
Joined
Sep 19, 2006
Messages
2
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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

chicagodiceman

Active Member
Joined
Mar 13, 2004
Messages
371
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.


Code:
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

New Member
Joined
Sep 19, 2006
Messages
2
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

Active Member
Joined
Mar 13, 2004
Messages
371
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!
 

Forum statistics

Threads
1,136,592
Messages
5,676,690
Members
419,644
Latest member
KeelsM

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