[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 702
  • Last Modified:

Subtotals in VBA

I'm trying to create a macro via VBA to copy my data from one tab to another and subtotal it. What I want to copy is columns A, B, C, D, E & G and then subtotal that by column A and then also give me totals (sum) for columns C & D. My headers are in row 3 and I also want to copy those as well. The amount of rows will vary in this tab since the data is updated monthly so I'm hoping it can be written to copy all rows of data. I also want to be able to specify which rows to copy based on the value in column A. This can be hard-coded into the VBA code and if it was to change I could just modify it as needed.

Example of the rows to specify from column A: "416SS", "416", "303SS"
So this would be hard-coded in the VBA code and I could add or delete to these values.

The values in Column A are formatted as general because they are a combination of numbers and text.

So I want to get a subtotals for columns C & D on this new tab it creates and have it also show the value it subtotalled in Column A along with the word "Total".

For example column A has a value of 416SS so on the subtotal tab it would say "416SS Total".

I'm very new to VBA so that's why I'm here. I'm hoping someone can help me write this code.
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 3
  • 3
  • 2
2 Solutions
 
JPIT DirectorCommented:
Can you provide a sample worksheet so we can see the existing layout and how you want it to look when completed. Just want to make sure you get a good solution on the first try.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Sure thing. Please see the attached sample. It has two tabs in it, 1 for the detail and 1 for the subtotals. You'll see how we currently do it. I just want to automate this process and only be able to select certain rows based on the criteria I supply to the vba code. I was thinking perhaps I could create a list on a hidden tab that has the values from column F that I want to copy and subtotal.

I changed some of the columns around but the one shaded blue is the one I want to use as the subtotal and the ones shaded yellow are the ones I want to total up on the summary tab.

On the summary tab there are some additional columns that have formulas in them. I'm not sure if we can incorporate this into the vba code.

I provided some sample data on the detail tab. The summary tab shows more detail than I have on the detail tab. It was an older version so I just left it there so you can see how we did it in the past.


Please let me know if you have any questions.
Sample-Data.xls
0
 
Jerry PaladinoCommented:
lsalvucci,
From the description of the results you want it seems like a Pivot Table will provide what you are looking for and does not require any VBA coding.  Pivot Tables are built in Excel functionality.   Since you moved some of the fields around in the Detail data worksheet I am not positive if I selected the exact columns that you wanted but any column from your detail data sheet can be added to the report just by dragging from the Pivot Table Field list to the report and dropping it in the location of your choice.  The drop down arrows at next to each field name in the report is a filter so any combination of data can be filtered.  From your question you wanted to be able to select only specific values from the "TYPE" field.   In the Pivot Table, click the drop down arrow next to TYPE and you can pick one or any combination of the available values to display.
When new data is added to the detail worksheet you just refresh the pivot table and it reads the data on detail tab.  The data for the pivot table is defined as a dynamic named range called Database that calculates the size of the data area in the Detail worksheet so the Pivot table knows if the data has increased or decreased.
Please review the attached file for a sample based on your data.
Jerry

Sample-Data-1-.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you for the response. I will review your idea. I'd still like to wait for peetjh to reply with his response before I choose an option. I would prefer the subtotal vba but I do like the pivot table idea. Of course I'll have to run both ideas by my boss.
0
 
JPIT DirectorCommented:
I will be able to get you something tomorrow. I'm afraid I don't have time to work on it today.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Not a problem whatsoever. I'm too busy shoveling snow here today.
0
 
JPIT DirectorCommented:
lsalvucci,

After looking over the desciption further and reviewing the information the ProdOps provided, I think that his solution is the way to go and requires less work. Sorry for getting your hopes up for another solution.
0
 
Jerry PaladinoCommented:
Peetjh - Thank you for your comments.
lsalvucci,

These articles / tutorials on Pivot Tables may help you understand the basics and the broad range of capabilities they have:
 
http://www.contextures.com/xlPivot01.html 
http://office.microsoft.com/en-us/excel/HA010346321033.aspx 
http://www.metacafe.com/watch/518324/excel_2003_pivot_table_101/  (video)
Jerry
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now