Copy all data that match a month and account number

**MAButler** · 03-30-2011, 04:07 PM

I need to copy the serial numbers and amounts from each row in a very large amount of data (4000 lines and growing daily) that match a user defined month and account number (Fill in a box that appears over screen - like a message box). Then paste that data into a new workbook in a new file and save using a set file name based on the account and month used.

This has taken me days to work out, and it only finds the account and pastes the whole line to a new sheet in the same workbook.......

I need some help.......

Please Login or Register  to view this content.

Account Date Amount Serial
4564 01/04/2010 90.00 23456
4578 01/04/2010 95.00 24186
4578 02/04/2010 60.00 24916
4578 02/04/2010 71.00 25646
4564 03/04/2010 43.00 27106
4582 03/04/2010 69.00 26376
4536 04/04/2010 78.00 27836
4578 04/04/2010 45.00 28566
4578 05/04/2010 40.00 29296
4582 05/04/2010 99.00 30026
4536 06/04/2010 45.00 31486
4578 06/04/2010 60.00 30756
4564 07/04/2010 36.00 32216
4564 07/04/2010 82.00 32946
4578 08/04/2010 66.00 33676
4578 08/04/2010 49.00 34406
4582 09/04/2010 53.00 35136
4582 09/04/2010 76.00 35866
4536 10/04/2010 3.00 36596

Thanks Mike

**nimrod** · 03-30-2011, 04:34 PM

can you upload a sample sheet ?

**nimrod** · 03-30-2011, 05:13 PM

Without a sample file I've had to make some assumptions ... but here's the general idea..

Assumptions:
- Row 1 Header values for all columns
- Data ends before Column X
- Data sheet is active and not protected
- All date ranges are for current year

This macro basically automates the use of "Advanced Filters" ... To understand Advanced filters check this page out http://www.contextures.com/xladvfilter01.html

Please Login or Register  to view this content.

**MAButler** · 03-30-2011, 08:10 PM

Thank you for this its great..........

I can see it now you have shown me.

I have made a couple of adjustments and its working fine, apart from two things.

1. The date needs a "year", as the data is from April to March.

2. When you reopen the saved file (AccountNum & "_" & MonthNum & ".xls") i get the following error message....
"The file you are trying to open is in a different format than specified by the extension......... "
I can click yes and the file opens, but is there any way to get rid of it.

Please Login or Register  to view this content.

**Leith Ross** · 03-30-2011, 08:30 PM

Hello MAButler ,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon

Select all your code using the mouse.
Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags

[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] After the last line.

**nimrod** · 03-30-2011, 10:39 PM

Modifications:
1) User now enter mm/yyyy instead of just month
2) Calculation for EOM updated
3) Error checking for mm/yyyy User Entry
4) Msg as end informs user of results i.e. file created , rows copied etc
5) Updated save method

Please Login or Register  to view this content.

**MAButler** · 03-31-2011, 06:32 AM

The 1st code you supplied worked fine at home on test set up, (Excel 2007).

But now i have set it up here on works pc's it creates the new file puts the header line but does not find the data. (the only difference is the work PC's run with Excel 97)

The 2nd code stops error 400??

Mike

**nimrod** · 03-31-2011, 11:05 AM

Could you upload a sheet so that I can take a look at it ?

**MAButler** · 03-31-2011, 11:13 AM

Yes when i get home......

But how do I attach it??????

**nimrod** · 03-31-2011, 11:16 AM

I need the exact file you are using at work .. and not home data/sheets. To upload file just click "Go Advanced" ... in that screen you will see a button about managing uploads.

**nimrod** · 03-31-2011, 11:18 AM

Another suggestion is to do an "Advanced Filter" manaully at work and see what happens. Another idea is to click the debug button when the errror occurs and see which line it is failing on. Also try it on another computer at work as see if you still have the problem

**royUK** · 03-31-2011, 11:22 AM

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**MAButler** · 03-31-2011, 01:24 PM

This is a small copy of the main database, at the moment the live one has 5200 rows of data and is updated every day.

You will see i have left the 1st code on, but i did quickly try the 2nd code but it also failed to pull the correct data over. I took the delete line out and looked at the advanced filter there was nothing in line 2 just titles in line 1?????????

**royUK** · 04-01-2011, 05:11 AM

I would think that a PivotTable would be the best approach, Group dates by Month

**MAButler** · 04-01-2011, 08:32 AM

Thanks RoyUK for that, but i need to make it as automated as possible......... The formula nimrod supplied is in the right direction........ just cant get it to work.......