Writing Macros in Excel

Question

I'm trying to create a macro in Excel 2013 through Developer. I need to be able to have it take information from multiple cells on Sheet 1 for a date that is recorded in the A column with the months across the top on the A row. I need them to take information put in column F and add it together and put it in the proper cell on Sheet 4, where it can be shown as a total. I'm not sure how to write a macro that would be able to do that for multiple pieces of information that need to consolidated on different sheets in my workbook. Please help. I'm working with different dollar amounts entered on Sheet 1 that will be added and totaled on Sheet 4. I know that there are three wildcard symbols I can use. I'm thinking that I'll need more than one to get this done right.

squeakysneakers · Answer

Bigtoes&nbsp;Hi Bigtoes,I am not sure I understand fully. By "Row A" you meant row 1, correct? I am assuming you did.For sheet 1, you put the date in the A column, and based on the fact it has a date, when you press "go" you want it to take the amounts in different cells and add them to the cell in F, then transfer that to a cell on sheet 4. Correct? Are all the amounts to be added in the same row? After the macro would run, what happens to the date on sheet 1? If you run the macro again, where would the next total be placed? Below the last one? On sheet 1, is there a column that has unique entries, like a part number or a name that is used only once?If there is a unique identifier column on sheet 1 you may be able to do this with formulas.

bigtoes · Answer

The dates, as in the day number (1,2,3...), are down column A from cell 2-32 and A1is labeled Date. Along the top row (Row 1), from B-M are the names of the months. That is all on Sheet 4. Column A on Sheet 1 is for a formatted date, ie: 01/01. The revenue from that date is recorded in Column F. The macro I need to create involves having the correct date and all of the dollar ampunts from a particular date. It would need to recognize a particular date for at. It would also need to add all of the dollar amounts from any day indicared by that format. The information would then be totaled and put in the proper cell on Sheet 4.

squeakysneakers · Answer

Are you able to provide a sample of the spreadsheet?

bigtoes · Answer

SqueakySneakersHere is a copy of the spreadsheet I'm working with. I have actual data that I need to assimilate. I just need to be pointed ion a direction that will allow me to transfer data between sheets as it's entered, instead of having to hit shortcut keys.

squeakysneakers · Answer

Bigtoes&nbsp;Hi Bigtoes.First thing I noticed was there is a "space" at the end of the word MAIN on the MAIN tab. I removed it from my copy. Spaces like that make formulas not work if they are not accounted for. On the Earnings tab I put a sumif equation in the first cell for you to show how it will add all of the 1/1/2020 amounts, and all the other amounts are for examples. The formula will drag and drop. On the Earnings tab, row 40, I added a year (a40) and the numbers represent the months. These are used to make the formulas work. You can hide this row. For February 29 I had to create a special formula that accounts for leap year so do not copy over that one. Let me know if this works.There are circular references in this spreadsheet so you will need to fix those. Ex. J35 has a formula that sums itself. (=sum(h35:j35)). Others have this same error.

Forum Discussion

Writing Macros in Excel

6 Replies

Resources