Avatar of jjc9809
jjc9809
 asked on

I need to add numbers in a column Jan through Dec and give Total using Excel Macro

I have a spreadsheet with Columns Jan through Dec.  I need a Macro Button that will add these numbers up by pressing the button and give a grand total.
Microsoft Excel

Avatar of undefined
Last Comment
jjc9809

8/22/2022 - Mon
Patrick Matthews

jjc9809,

Why can't it be done with a formula?

Patrick
jjc9809

ASKER
I have several worksheets in a workbook called Prior Year Total Sales Oct -- Sep10 Fiscal Year.  I have totals for all of last year and I have worksheets for the Current Fiscal Year Oct-- Sep11.  I want to have totals calculated for the prior year based on what month I am in the current fiscal year.  For example, I am doing comparison bar graphs for Oct -- June 10 with Oct-- June 11.  I want to click a macro button and total the columns Oct--Jun10 to bring into my current year bar graph cells Prior Vs Current.  

I will have others entering data in for the months, so I want to have the Oct--Jun 10 calculated automatically with a click of a macro button so the user want have to do any further calculation.

I want the Prior Year Worksheets hid from view.

jjc9809
Swapnil Nirmal

plz post a sample worksheet.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Patrick Matthews

All of that can be done with formulas.  Indeed, you are far better off doing it with formulas, because then your spreadsheet will still work even if macros are disabled.

Please post a sample file.  Be sure to remove/obfuscate any sensitive data first.
jjc9809

ASKER
jjc9809

ASKER
I posted an Excel File called Example
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Patrick Matthews

jjc9809,

You wrote in http:#a36156794:

I have several worksheets in a workbook called Prior Year Total Sales Oct -- Sep10 Fiscal Year.  I have totals for all of last year and I have worksheets for the Current Fiscal Year Oct-- Sep11.  I want to have totals calculated for the prior year based on what month I am in the current fiscal year.  [...]  I want the Prior Year Worksheets hid from view.

Where are all of those worksheets?  You did not include them in the sample file.  Are your people doing their data entry in the summary sheets, or in the detail month-by-month sheets?

Patrick
ASKER CERTIFIED SOLUTION
Patrick Matthews

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jjc9809

ASKER
I will upload the main excel file I am using.  I will make up data in the sheets to show you first hand what I am trying to do.
jjc9809

ASKER
There should be a macro button for each Month Oct through Sep.  If the user wants to see totals for June, he would click the June button which would give total prior year sales Oct through June.  If the user wants to see Total Sales for July, he would click the July button and have totals calculated for Oct through July and so on.  The user will enter data manually for each month of the current year. I am trying to cut down on complicated work here for the user.  Sure I could use formulas and have all sheets in there and link up for myself I know excel, but the user who will be entering this data does not know excel very well.
STORE-SALES-BY-070711A.xlsx
Your help has saved me hundreds of hours of internet surfing.
fblack61
jjc9809

ASKER
I need for the macro buttons to be on the Bar Chart sheets in the file.
Patrick Matthews

jjc9809,

In your question you wrote:

I have a spreadsheet with Columns Jan through Dec.  I need a Macro Button that will add these numbers up by pressing the button and give a grand total.

Now you're talking about multiple buttons (see http:#a36159041).

Sorry to be a pain, but just what is it that you're trying to accomplish here?

Patrick
jjc9809

ASKER
I will need macro buttons for Oct through September on the Bar Chart Sbeets only that will allow the user to select a particular month based on the Current Year To Date Month at hand.  If the Current Year to Date Month is June 2011, I need a Macro Button that will sum all Totals for all six Districts by months Oct 2009 through June 2010.  The reason for the Macro buttons is that I have been told that the user does not want to see so many spreadsheets that they will be confused since they are not accountants.  The way I had the Bar Chart application working was to use fromulas as you suggested which in my opinion does work better, but the user only wants to enter Current Year months in a spreadsheet.

The only spreadsheets the user will enter data in manually is the Current Year To Date Spreadsheets called Total Sales Oct10--Sep11, Retail Sales Oct10--Sep11, and Wholesale Sales Oct10--Sep11.  The user at the end of July will enter July Numbers for the current year now.  When the user clicks the July Macro button on the Bar Chart Sheets, I want the months Oct 09 -- July 10 summed to give me prior year numbers so as to compare the same period last year with the same period this year.  I hope this makes better sense.  I am an accountant so its hard to tell programmers what I actually want.

Once I get the coding behind one Command Button, I can copy the formula for the other buttons.

I hope this writing has helped.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
jjc9809

ASKER
Oh, the Prior Year Spreadsheets called Prior Total Sales, Prior Retail Sales, and Prior Year Wholesale Sales will be hidden from the user.  Also, the other prior year spreadsheets will be hidden also.  The macro buttons selected will total these numbers behind the scenes and place the prior year total in the right cell for Total Sales, When Retail Sales, and Wholesale Sales Prior Year.  

When the suer enters current year to date numbers, the bar charts should form and make based on the numbers inserted by the macro buttons and the formula totals for Current Year I have placed in there based on links.