VBA Code - Loop through rows one by one copy perform calculation and paste in another sheet

[rachugh86]

Need help in coming up with VBA code logic . My requirement is as follows:

Sheet1 (Input Sheet)

ProjectID ProjectName 2022 2023 2024
Project 1 Name A 100 200 300
Project 2 Name B 400 500 600
Project 3 Name C 700 800 900

Sheet2 (1st Output Sheet Read the first entry from Sheet1)

ProjectName Name A
Values 100 200 300
Calculation 50

Sheet3 (2nd Output Sheet Read the calculated value from Sheet2)

ProjectName Calculated Value
Name A 50

Now repeat the entire process for remaining projects as below making sure Sheet2 data is cleared

Sheet2 (1st Output Sheet Read the first entry from Sheet1)

ProjectName Name B
Values 400 500 600
Calculation 150

Sheet3 (2nd Output Sheet Read the calculated value from Sheet2 and add new row each time for projects)

ProjectName Calculated Value
Name A 50
Name B 150