VBA Coding - Cycle through list data in single column on one sheet and paste into specific cell on another sheet

Question

I'm new to coding so please let me know if what I'm trying to do doesn't make sense.

I'm trying to pull data from a single column in a sheet labeled "Macro." This sheet contains a list of dependent values used to run a calculation on the second sheet labeled "Calculation." I have 1 specific cell ("B5") in the "Calculation" sheet that I want to pull the information from "Macro" into. The following code is able to perform the function I want, but only for one dependent from the "Macro" sheet:

Sub A_Equals_B_Across_Sheets()
Sheets("Calculation").Range("B5").Value = Sheets("Macro").Range("B2").Value
CopySheetAndRenameByCell2
End Sub
Public Sub CopySheetAndRenameByCell2()
    Dim wks As Worksheet
    Set wks = ActiveSheet
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wks.Range("B5").Value <> "" Then
        On Error Resume Next
        ActiveSheet.Name = wks.Range("B5").Value
    End If

    wks.Activate

End Sub

Effectively, I want to this code to loop through each cell in the dependent category from the "Macro" sheet. It will run to pull the number from "B2" in the "Macro" sheet into cell "B5" of the "Calculation" sheet, perform the rename/sheet copy, loop back up to the top of the code and then it will pull in "B3" to cell "B5" again, run through the copy/rename, so on until the list is exhausted. Is there an easy way to do this or will I have to copy and paste this code, stringing each together with only changing the "Macro" range?

Thanks in advance for any help!