twmills
04-20-2022, 12:25 PM
Hello,
This one has a lot of moving parts, but I think it's doable. I'm looking to put a macro in the Output spreadsheet that pulls in data from the Proforma spreadsheet. Examples of both files are attached.
In the Proforma spreadsheet there's two rows of data that's needed, row 74 (Class A) and row 75 (Class C). These amounts are given for each fund, listed in row 1 across the top (Four letter code). I'm only interested in the amounts listed in the column with any fund that ends in an "A". For instance, column D (CVWGA), column M (CVWHA), column V (CVWJA), etc...
I'm looking for the macro to take those amounts and paste them to their corresponding spot on the Output file (Data Entry - USBFS tab). So all the Class A amounts - row 74 of the Proforma spreadsheet - will go in rows 49 through 67 in the Output file. And the Class C amounts - row 75 of the Proforma spreadsheet - will go in rows 86 through 104. Column B in the Data Entry tab has the Fund listed (without the "A") as a reference, so we know which amount goes in which row exactly.
To add a wrinkle to this, the Output file will just hold the data for a quarter (3 calendar months), while the Proforma spreadsheet is provided every month. So, after the 3rd month of each quarter, the Output file is wiped clean to make room for the next quarter's data. Column E, G and I, of the Output file, will hold the data for the 1st, 2nd and 3rd month for each quarter. With that being said, it'll be broken down like this by month number:
1st Month (column E) will house data for months: 1, 4, 7, 10
2nd Month (column G): 2, 5, 8, 11
3rd Month (column I): 3, 6, 9, 12
On the Data_1 tab of the Output file, the current month can be placed in cell B30 (this will be done manually), then the "Month in quarter" will be calculated in cell B31. This might be useful to use as a pivot for VBA coding.
Since the example I have for the Proforma spreadsheet is for March, I manually copied and pasted the first 5 funds in their correct spots in the Output spreadsheet (column I - 3rd month), as as reference. I removed a bunch funds in Proforma spreadsheet to shorten it up. it was too large to attach at first. So each fund in the Output file won't have a corresponding amount in the Proforma spreadsheet.
Sorry this is so wordy and complicated....Thanks so much!!!
This one has a lot of moving parts, but I think it's doable. I'm looking to put a macro in the Output spreadsheet that pulls in data from the Proforma spreadsheet. Examples of both files are attached.
In the Proforma spreadsheet there's two rows of data that's needed, row 74 (Class A) and row 75 (Class C). These amounts are given for each fund, listed in row 1 across the top (Four letter code). I'm only interested in the amounts listed in the column with any fund that ends in an "A". For instance, column D (CVWGA), column M (CVWHA), column V (CVWJA), etc...
I'm looking for the macro to take those amounts and paste them to their corresponding spot on the Output file (Data Entry - USBFS tab). So all the Class A amounts - row 74 of the Proforma spreadsheet - will go in rows 49 through 67 in the Output file. And the Class C amounts - row 75 of the Proforma spreadsheet - will go in rows 86 through 104. Column B in the Data Entry tab has the Fund listed (without the "A") as a reference, so we know which amount goes in which row exactly.
To add a wrinkle to this, the Output file will just hold the data for a quarter (3 calendar months), while the Proforma spreadsheet is provided every month. So, after the 3rd month of each quarter, the Output file is wiped clean to make room for the next quarter's data. Column E, G and I, of the Output file, will hold the data for the 1st, 2nd and 3rd month for each quarter. With that being said, it'll be broken down like this by month number:
1st Month (column E) will house data for months: 1, 4, 7, 10
2nd Month (column G): 2, 5, 8, 11
3rd Month (column I): 3, 6, 9, 12
On the Data_1 tab of the Output file, the current month can be placed in cell B30 (this will be done manually), then the "Month in quarter" will be calculated in cell B31. This might be useful to use as a pivot for VBA coding.
Since the example I have for the Proforma spreadsheet is for March, I manually copied and pasted the first 5 funds in their correct spots in the Output spreadsheet (column I - 3rd month), as as reference. I removed a bunch funds in Proforma spreadsheet to shorten it up. it was too large to attach at first. So each fund in the Output file won't have a corresponding amount in the Proforma spreadsheet.
Sorry this is so wordy and complicated....Thanks so much!!!