Forum Discussion

Bigtoes's avatar
Bigtoes
Copper Contributor
Aug 20, 2020

Writing Macros in Excel

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 ...
developer
excel
Macros and VBA
Bigtoes's avatar
Bigtoes
Copper Contributor
Aug 20, 2020
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's avatar
SqueakySneakers
Brass Contributor
Aug 20, 2020
Are you able to provide a sample of the spreadsheet?
  • Bigtoes's avatar
    Bigtoes
    Copper Contributor
    Aug 20, 2020

    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's avatar
      SqueakySneakers
      Brass Contributor
      Aug 21, 2020

      Bigtoes 

      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.

      • SqueakySneakers's avatar
        SqueakySneakers
        Brass Contributor
        Aug 21, 2020
        Also, if you change the year in cell A40, it will work for that year, but for only one year at a time.

Resources