Hi,
I have attached a sample spreadsheet to assist with my problem.
I am needing to run a macro to look through the 3 worksheets and list any accounts that do not total 100% (cell D35) into the summary tab. Ideally this would then list the code of the account (cell C5), the name of the account (cell C3) and the total percentage (cell D35) into the Summary tab.
Any help would be much apprecitaed!
Eoghan
Try this code
Copy the Excel VBA code
Please Login or Register to view this content.
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button
If you have fresh sheets inserted or some values changed and you want to re-run the summary macro, do you want the summary sheet to be cleared first and then data input?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Thanks for your help.
Yes, thats the idea. However, I will create a seperate macro to clear all the data.
The Test spreadsheet I've shown here will be different from the one I am using. I will actually have the 'Summary' tab in a seperate spreadsheet. Lets just say I call that spreadsheet Test2. How do I use the loop you have used in the code to refer to the Test spreadsheet?
I have also noticed in your code that it is for all values less than 100%. In some circumstances the values can be greater than 100%. Is there a way to add these two conditions in?
You do not need to create a separate macro, you can include it in the above code itself. Add these 2 lines after the DIM statementsOriginally Posted by eoghanmolloy
Please Login or Register to view this content.Do you mean a separate workbook? Do you want the macro to open the workbook or will it already be open?The Test spreadsheet I've shown here will be different from the one I am using. I will actually have the 'Summary' tab in a seperate spreadsheet. Lets just say I call that spreadsheet Test2. How do I use the loop you have used in the code to refer to the Test spreadsheet?
Do you want both the conditions in the same macro?
Hi Arlete,
Thank you so much with all your help.
I have attached 2 spreadsheets as an example of what I'm after. I need to have a macro in Test2 that will look through Test and detail accounts which have a percentage discrepancy i.e cell D35 is not equal to 100% (sometimes it can be less than and sometimes it can be greater than 100) and list the accounts details back into the Test2 spreadsheet.
On the Test spreadsheet there are 3 tabs but on the spreadsheet im working on there are over 80. I take it the code you have used so far won't make a difference for the amount of tabs there are?
The macro does not need to open Test2 as this will already be open.
If you can add the two conditions into the one macro that would be great.
If you can add the code to refresh the data to start of with that would be great to.
Your help is much appreciated.
Thanks,
Eoghan
Updated code -
This code will take into account any number of sheets. Ensure that the macro is copied into a standard module of the Test workbook.Please Login or Register to view this content.
Excellent Arlette!! Thank you for your help.
I don't know if you can help me with my next problem or recommend starting a new thread...
I need to try and come up with a method (im thinking a macro will be the best bet) to add new accounts to the Test spreadsheet.
This would mean say a worksheet on Test2 contained new data in which there isn't a corresponding tab on Test, is there a way to create a macro that will create a new worksheet (naming it appropriately to the code of the new account) and the feeding through all the new information?
Let me know if you can help and if you'd like an example spreadsheet I'll be more than happy to supply.
Thanks,
Eoghan
It will be better if you create a new thread for that.
You can mark this thread as solved - Select Thread Tools -> Mark Thread as solved.
Paresh,
Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks