Excel 2007 : Range selection in multiple Worksheets

[blackpoolrocks]

Hi

I'm pretty good with Excel but complete novice with the macro/vba side of things, I've found responses which appear to be answers to my responses but my experience at building macros is so low I simply dont understand the posts ive read, so im hoping there is someone out there who has the time to either guide or help a struggling macro newbie. My issue is this

I have a workbook with several worksheets - the columns in all the worksheets are the same but represent different projects eg. w/s 1 is pigs, w/s 2 is cows and w/s 3 is horses. They all have the same column titles in the same columns eg. col a is name, col b is farm, col c is age etc.

I want to add a summary sheet which automatically updates or can be updated with a macro but my issue is that w/s 1 might contain 100 rows and w/s 2 may contain 67 and w/s 3 contains 450.

I would like the summary sheet to display the contents of all the w'sheets in one place so i can then apply an autofilter and list the data however i want.

I could just kick off a macro, select A1 to Z500 for each worksheet, copy and paste to the summary sheet, then sort, leaving all the blanks at the bottom, but some cells have formulas in and would still interfere with the sorting afterwards.

Ideally, I would like to press a button, the macro gos to w/s 1 selects cols a-z, looks in col G (for example) and as soon as it hits a cell that contains no data (eg row 30), it auto selects a1 to z29, copies the data and pastes it to the summary sheet, the macro then goes off to the secon w/s and does the same thing and drops it on the next free row of the summary sheet and then repeats for the rest of the w/sheets.

Looking at it now, it does look like it might be difficult actually. But like i said, if you give me a load of code etc, i dont even know where to start with adding it to the macro, but i do know how to record one and run it based on mouse clicks etc.

Be great if someone can help with this

Thanks

Simon

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[blackpoolrocks]

Ive uploaded a dummy of the file - as you can see on the summary page ive literally paste linked nearly 2000 rows to the summary page. If I wanted to sort by priority i could do with the autofilter but depending on if i chose a-z or z-a, the results would harbour the valid cells either right at the top or right at the bottom so id need to scroll down 2000 rows to get my results. The only data i want on that summary page is valid cells with data in. I hope the example here helps

[blackpoolrocks]

not sure if the file has uploaded so posted it here too - http://www.rolleys.com/mp3s/formrexcel.xls

[davesexcel]

Sorry, I am not going to open the workbook from a 3rd party source, also keep the sample workbook simple, I see that the workbook you want us to download is over 2 mb.

[blackpoolrocks]

Apologies

I tried to upload it and it seemed to work but i dont see the attachment anywhere. Ive whittled it right down to a few sheets and have attached to this reply. I have also re explained what i want to happen and have put a before and after page as you have suggested.

Thanks again for your patience

Simon

[davesexcel]

Give this a try,
I delete the blank formulas to make the workbook smaller for uploading.

[blackpoolrocks]

WOW - Dave - that is amazing, i renamed tabs and added additional worksheets and it picked everything up. Absolutely AMAZING!!!. Thankyou so much for your help.

[blackpoolrocks]

Dave. I thought everything was running fine but when I have copied this macro to another sheet and started using it I noticed it got slower and slower and the file is growing and growing at a massive pace and is currently 8.26MB, is there something in the macro which is adding to the file every time i run it?

[blackpoolrocks]

Just tested the s/sheet you posted above and this happens in yours too, just click it 4 or 5 times and the file size will double - any ideas? Thanks

[blackpoolrocks]

sorry to be a pain, but one other thing it doesnt do, is miss out worksheet 2 for the copying / pasting exercise - i wanted to leave this one for something else - is that easy? sorry and thanks for your time

[davesexcel]

The workbook does not get larger for me, I ran the code about 30 times and saved it again, it was 1 kb smaller.

[blackpoolrocks]

Strange. I just closed down excel, deleted the file, closed all apps and rebooted. Downloaded your file again from this thread and did a save as on the desktop.

Re-opened it, hit the button 6 times and moved the file size from 127K to 920KB, pressed again 1076KB, again 1210KB, again 1344KB, again 1477KB. I'm using Excel 2007 SP2 MSO in Compatibility Mode, would this make any difference? BTW. If I go to the summarry sheet and highlight every row after the last one. eg. 23-1000 and right click, delete, then save the file, the file shrinks to 419KB????

[blackpoolrocks]

Opened in 2003 and this worked perfectly - why is it that this script does not function the same in 2007 Dave, any ideas? as this is my current preferredexcel app