Reformat a table

[johnnyjohn]

Hi Fellas,

I am trying to reformat a table from <Bonds Cash Flow> to <Cash Flow>

The result that I am trying to achieve is that :

Input :



Year
Coupon
Principal


MATGFI
27,000,000.00
21,026,250.00


2019
-
203,750.00


2020
-
922,500.00


2021
-
922,500.00


2022
-
922,500.00


2023
-
922,500.00


2024
-
922,500.00


MATGIL
70,371,250.53
6,208,790.23


2019
-
59,531.19


2020
-
272,734.48


MATNGFI
484,259,999.99
219,039,076.04


2019
6,101,425.99
4,379,849.22


2020
17,166,120.06
19,430,226.04


2021
30,324,901.19
18,609,592.25


2022
32,405,217.91
17,461,785.16


2023
34,225,052.89
16,309,053.31


2024
26,562,865.39
14,944,764.41


Output:


Portfolio
Year
Coupon

Principal


MATGFI
2019
-
203,750.00


MATGFI
2020
-
922,500.00


MATGFI
2021
-
922,500.00


MATGFI
2022
-
922,500.00


MATGFI
2023
-
922,500.00


MATGFI
2024
-
922,500.00


MATGIL
2019
-
59,531.19


MATGIL
2020
-
272,734.48


MATNGFI
2019
6,101,425.99
4,379,849.22


MATNGFI
2020
17,166,120.06
19,430,226.04


MATNGFI
2021
30,324,901.19
18,609,592.25


MATNGFI
2022
32,405,217.91
17,461,785.16


MATNGFI
2023
34,225,052.89
16,309,053.31


MATNGFI
2024
26,562,865.39
14,944,764.41


MATNGFI
2025
40,718,645.16
13,783,117.66


MATNGFI
2026
37,858,132.78
12,320,225.84


MATNGFI
2027
15,743,813.95
10,928,934.92


I managed to write those first lines to create the column A BUT I am struggling to find how to copy.paste the range from one tab to another following the lenth of each Portfolio.
The portfolio names are fixed, but the year (so the number of rows) can change over the different period, then output table too.

Is any one has an idea how to do that ?

Thank you !

John


Sub FindString()
Dim i As Integer


With Worksheets("Bonds Cash Flow").Range("A:A")
Set a = .Find("MATGFI")
aAddress = a.Address
arow = a.Row
Set b = .Find("MATGIL")
bAddress = b.Address
bRow = b.Row
Set c = .Find("MATNGFI")
cAddress = c.Address
cRow = c.Row
Set d = .Find("MATNGIL")
dAddress = d.Address
dRow = d.Row
Set e = .Find("blank")
eAddress = e.Address
eRow = e.Row
End With

Sheets.Add.Name = "Cash Flow"
Range("A1") = "Portfolio"
Range("B1") = "Year"
Range("C1") = "Coupon"
Range("D1") = "Principal"

For i = 1 To (bRow - arow - 1)
Sheets("Cash Flow").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Sheets("Bonds Cash Flow").Range(a.Address)
Next i

For i = 1 To (cRow - bRow - 1)
Sheets("Cash Flow").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Sheets("Bonds Cash Flow").Range(b.Address)
Next i

Sheets("Cash Flow").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Sheets("Bonds Cash Flow").Range(Cells(bRow, 2), Cells(cRow, 4))

For i = 1 To (dRow - cRow - 1)
Sheets("Cash Flow").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Sheets("Bonds Cash Flow").Range(c.Address)
Next i

Sheets("Cash Flow").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Sheets("Bonds Cash Flow").Range(Cells(cRow, 2), Cells(dRow, 4))

For i = 1 To (eRow - dRow - 1)
Sheets("Cash Flow").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Sheets("Bonds Cash Flow").Range(d.Address)
Next i

Sheets("Cash Flow").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Sheets("Bonds Cash Flow").Range(Cells(dRow, 2), Cells(eRow, 4))

End Sub



Microsoft
25295

[Paul_Hossler]

Make it easy for us by

1. Using CODE tags

2. Attaching a sample workbook with the input data, the macros, the output data, and the process steps spelled (spelt ?) out

[johnnyjohn]

Thanks done ;)

[Paul_Hossler]

Off the top of my head, it seems like your pivot table could do what you want

If I'm missing something, then come back

25297

25298

25299

25300