Is what I'd like to do even possible?

Ergocorp

New Member
Joined
May 2, 2019
Messages
4
Hi, I've taken plenty of advise from this forum, so much so that I've only needed to register now, so thank you all.

I get data from external sources in the form of a table that has every month of a year. I'm looking for a way to automate the transfer of a specific month's sum to another spreadsheet, and then again with certain filters on. The table might increase in number of rows every month I receive it eg.

NameFilter 1Filter 2JanFebMarAprMayJunJulAugSepOctNovDec
1ax55
2ay2
3az323
4az12
5bz545
6by2222
7bx41
8ax45
Total17121812

<tbody>
</tbody>
Is this even possible with VBA and a macro? Would I need to clean the table somehow?

Thanks.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,539
How do you decide which month and where on the other sheet do you want to transfer the month's sum?
 

Ergocorp

New Member
Joined
May 2, 2019
Messages
4
I do a monthly report, so I get that data every month. In essence the data I get is the same as the previous month with an extra month. Because this is a report on the previous month, I'd want current month -1. I get 10 sets of data each a tiny bit different depending on their own systems and little quirks. I have an aggregation work book where I collate the data, sums up the filters across at 10 etc. I then have a master workbook that I transfer that data to using macros that forms a dashboard. I'm conscious I could lose the aggregation workbook and take the data directly, but one step at a time :)
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,539
To clarify: This is the month of May so you want to find the total for April from the row that has "Total" in column A. If this is correct, I'm still not clear on where you want to paste this total for April. Do you want to transfer the total to your master workbook? If so, what is the full name of the master workbook including extension (xlsx, xlsm), what is the name of the destination worksheet in the Master and in what cell in that sheet do you want the total?
 

Ergocorp

New Member
Joined
May 2, 2019
Messages
4
I'm not really able to broadcast that on the internet, so I've tried to as generic as possible. It's not just May for example. When I come to do the report for say September, I want to total up all the rows for that month, and for November's report to count up the rows for that one.

There's already a total, why don't I just use that? Not all of the external data sources use a total.


Would I just be better creating specific macros for each source, then doing some sort of aggregation macro? I think so. I'm waffling here as I think I'm coming up with a way to do it that way I want, sorry for wasting your time.

How do you mark this as answered?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,539
Unfortunately, you can't mark the thread as solved in this Forum. Not to worry. You didn't waste my time. :)
 

Ergocorp

New Member
Joined
May 2, 2019
Messages
4
Ok, so I have been able to do some of the Formulae. Using INDIRECT and COUNT I was able to create a sum range that counts the number of rows, but I'm struggling when it comes to the column. Ignoring that data transfer to another work book element, assuming we don't have a total row, the above example would allow for a variable number of rows:

=SUM(G2:INDIRECT("G"&COUNT(A:A)))

However my next step is a variable column. The columns are headed with the month, as per the above example. I get a new version every month, increasing in row number (thus the need for that variable number), but filling out a new column of data. Si in this case, it was Apr. Replace 'G' with 'H' and you'd get May etc. Any one any idea how I pick out a specific column with a catch all macro?
 

Forum statistics

Threads
1,082,501
Messages
5,365,942
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top