edit my macro to total a column 4 rows below the last active cell

  • I have created a sreadsheet with a macro to total the sales, tax and tips, for both am and pm shifts. However, when I created the macro i recorded it to total in aspecific cell, instead of after the last cell that contained data. So if I have more sales during a shift than the one when I recorded the macro, it will only total up to the original cell.

    I tried editing the code to add "& LastRow + 3" but that just gave me nothing. Here is my current macro, can any of you help me?

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • I haven't got a clue what the code referring to another workbook is doing, but the whole code needs a major cleanup.

    I can't see where you use LastRow.

    Why don't you simply create a workbook template properly designed.

  • royUK, the biggest reason "I dont create a workbook template properly designed," is because I am not a superuser. I stepped way out of comfort zone doing this in the first place, thank you. Second, I followed the directions as provided in the "Help" function within Excel for how to record a Macro.

    So for background, my daughter has a program that will spit out a report in an excel spreadsheet of the sales per shift. I wanted to create a spreadsheet that she could cut and paste that into, that had a Macro, that would total the sales per shift, tax per shift, and tips per shift. Hence the reference to the other spreadsheet. Some days there will be more sales per shift than other days. What I wanted to create in my poorly recorded Macro, was a sheet with a Macro that would go to the last cell in each column for sales, tax and tips that had anything in it, then it would skip down 4 blank cells and insert the total of that column. When I recorded the poorly designed Macro, I had a sheet with only 60 or so lines, so cell 64 was adequate. But the next day, the morning shift had 70 lines of sales. So, my poorly designed Macro wouldn't work. Stepping further out of my comfort zone, I Googled what I was looking for an came across your forum, and found someone that had requested something very similar, but not quite the same. I opened the "Edit" screen of the poorly designed Macro and copied it into a Word document and looked at it next tot he code the the other helpful person had provided the other querant. I saw where it said something along "& LastRow + 3." I tried pasting that into different places that LOOKED like it would run that command, but to no avail; so I registered in this forum in order to ask you all.

    I'm sorry my poorly designed Macro was offensive in its simplicy and awkwardness, but it was the best I knew how to do.

  • We all (well, most of us anyway) started the same way you did and guess what, we all learned as we went along.

    The way to go in my opinion is to attach a workbook with a before and after, different sheets if that suits you, and an explanation in it that explains how you arrived at the answers.

    My personal preference is to have everything in one workbook. I usually have a "data" sheet with all info and have the result in another sheet. Mind you, having it in the same sheet off to the side is possible also of course. Other people have different ideas but there are many roads leading to Rome and they all get there.

    The gentleman from Post #3 has a world of experience so stick close to what he suggest

  • A template doesn't require you to be a super user, in fact it would be easier than creating a macro with no VBA knowledge.

    Attach an example workbook then we can better see the options available

  • A template doesn't require you to be a super user, in fact it would be easier than creating a macro with no VBA knowledge.

    Attach an example workbook then we can better see the options available

    Have you had a chance to look at this? thanks

  • You have two totals, one immediately below and not totaling the whole column.

    Your problem could be solved without macros by simply converting to an Excel Table which can have a Total Row automatically. A Table is dynamic and so will expand as data is added and the Total row will move as well.

    Overview of Excel tables

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!