VBA Help - Match x and y axis, and pull data

[twmills]

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!!!

[twmills]

uh-oh...so I'm not the only one stumped by this?

[Aussiebear]

May I suggest that you proceed by start recording a macro, with each of the moves you make to achieve your outcome, stop recording the macro and then post your attempted code. That way it may assist others to follow what you are attempting to do.

[twmills]

May I suggest that you proceed by start recording a macro, with each of the moves you make to achieve your outcome, stop recording the macro and then post your attempted code. That way it may assist others to follow what you are attempting to do.

Sure, but it'll just be a lot of manually copy and pasting. That's the only work around as of right now. We're trying to automate it based on what month it is. However, it's very possible that my original post wasn't as clear as I thought it was. :yes

The macro "Example" shows what I'm trying to copy where, on the Output spreadsheet. So what's currently on the Output spreadsheet - column I in yellow (Data Entry tab) - would be the end results. The results are going in column I because the proforma data is for March, which is the 3rd month of the quarter.

Spreadsheets are attached below.

Thanks!

[p45cal]

Maybe this macro will give you a start.
It looks at cell B30 of the Data_1 sheet to determine which column to fill in on the Data Entry - USBFS sheet.
It lets you pick your Proforma file (but doesn't open it).
See comments in the code.
If it balks on the lines beginning:
.Formula2R1C1 =
try changing them to:
.FormulaR1C1 =
It overwrites whatever was in those columns but does not do a 3-monthly clearing of data - you can do that manually (I'm only giving you a start).

Sub blah()
FilePathx = Application.GetOpenFilename("Excel files (*.xlsx), *.xlsx", , "Choose Proforma file")
If FilePathx <> False Then
ShtName = "MTD Expense Ratios" 'assumes the sheet is always called this.
x = InStrRev(FilePathx, Application.PathSeparator)
fname = Mid(FilePathx, x + 1)
fpath = Left(FilePathx, x)
MnthNo = Sheets("Data_1").Range("B30").Value 'check sheet name is correct.
DestnColmNo = ((MnthNo - 1) Mod 3) * 2 + 5

With Sheets("Data Entry - USBFS") 'check sheet name is correct.
With Intersect(.Rows("49:67"), .Columns(DestnColmNo))
.Formula2R1C1 = "=INDEX('" & fpath & "[" & fname & "]" & ShtName & "'!R74,,MATCH(RC2 & ""A"",'" & fpath & "[" & fname & "]" & ShtName & "'!R1,0))"
.Value = .Value
End With
With Intersect(.Rows("86:104"), .Columns(DestnColmNo))
.Formula2R1C1 = "=INDEX('" & fpath & "[" & fname & "]" & ShtName & "'!R75,,MATCH(RC2 & ""A"",'" & fpath & "[" & fname & "]" & ShtName & "'!R1,0))"
.Value = .Value
End With
End With
Else
MsgBox "Aborted"
End If
End Sub

[arnelgp]

it will be unfair if we Do It All for you.
you got the working formula and got paid.
anyway, it's part of your job?

[twmills]

Maybe this macro will give you a start.
It looks at cell B30 of the Data_1 sheet to determine which column to fill in on the Data Entry - USBFS sheet.
It lets you pick your Proforma file (but doesn't open it).
See comments in the code.
If it balks on the lines beginning:
.Formula2R1C1 =
try changing them to:
.FormulaR1C1 =
It overwrites whatever was in those columns but does not do a 3-monthly clearing of data - you can do that manually (I'm only giving you a start).

Sub blah()
FilePathx = Application.GetOpenFilename("Excel files (*.xlsx), *.xlsx", , "Choose Proforma file")
If FilePathx <> False Then
ShtName = "MTD Expense Ratios" 'assumes the sheet is always called this.
x = InStrRev(FilePathx, Application.PathSeparator)
fname = Mid(FilePathx, x + 1)
fpath = Left(FilePathx, x)
MnthNo = Sheets("Data_1").Range("B30").Value 'check sheet name is correct.
DestnColmNo = ((MnthNo - 1) Mod 3) * 2 + 5

With Sheets("Data Entry - USBFS") 'check sheet name is correct.
With Intersect(.Rows("49:67"), .Columns(DestnColmNo))
.Formula2R1C1 = "=INDEX('" & fpath & "[" & fname & "]" & ShtName & "'!R74,,MATCH(RC2 & ""A"",'" & fpath & "[" & fname & "]" & ShtName & "'!R1,0))"
.Value = .Value
End With
With Intersect(.Rows("86:104"), .Columns(DestnColmNo))
.Formula2R1C1 = "=INDEX('" & fpath & "[" & fname & "]" & ShtName & "'!R75,,MATCH(RC2 & ""A"",'" & fpath & "[" & fname & "]" & ShtName & "'!R1,0))"
.Value = .Value
End With
End With
Else
MsgBox "Aborted"
End If
End Sub

Yes, this is what I was looking for.

Thank you so much for your time on this. I really appreciate it, as always.