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
test reshape pivot table.xlsm

[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

Capture.JPG

Capture2.JPG

Capture3.JPG

Capture4.JPG