Macro required to identify under/overspends

[francohoops]

Hi
I am trying to write/record a macro that looks across a number of spreadsheets , 3 or 4, and pulls through onto one spreadsheet any under or overspends per programme between certain thresholds on a single spreadsheet.
Ideally I would like to be able to adjust the thresholds, but if this is too difficult then a set value would be fine.
Any ideas?
Thanks
F

[Leith Ross]

Hello francohoops,

Welcome to the Forum!

It is difficult to answer your question when you have not provided a workbook or any examples of the data. Please post a copy or your workbook.

To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture

[francohoops]

Please find attached an example of what I need to re-create as a macro. I have simplified what was a massive s/sheet with 100s of rows and cols, but the principle is the same.
There are a huge amount of offices/projects, and we want to be able to run a macro that identifies under and over spends and summarises neatly on a single sheet (the unders and over summary) which will go to management. It would be useful to be able to adjust the parameters so that we could pull out different offices/projects, but if this adds too much work then we can do without.
If anyone has any questions, or suggestions please let me know.
Thanks for your time
regards
F

[Leith Ross]

Hello francohoops,

I take it the sheet "Unders,_Overs" is the summary sheet, yes?

[Leith Ross]

Hello francohoops,

I have added the macro below to the atached workbook. A button has been added to the "Overs_Unders" sheet to run the macro.

Please Login or Register  to view this content.

[cinellistar]

This looks great but where in the code can you change the range that the array loops through? So what I am sayig is what if the following continent worksheets have more columns say up to column BG. How do you change the range on the CurrentRegion? I'm very new to VBA!

Many thanks!