Forum Discussion
Writing Macros in Excel
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.
- SqueakySneakersAug 20, 2020Brass ContributorAre you able to provide a sample of the spreadsheet?
- BigtoesAug 20, 2020Copper Contributor
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.
- SqueakySneakersAug 21, 2020Brass Contributor
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.