VBA Copy Paste Value One Cell At a Time from One Sheet to Another

[luke1438]

Hello

Using Windows 10, Office 2007

I have recorded the below code to demonstrate what I am trying to achieve. This has to do with loan withdraws and payments. If I use formulas it will cause circular errors. I am copying the contents of cell E108 (there is a formula in this cell that calculates the needed loan withdrawal) from my Cash Flow sheet and paste valuing it into cell C8 in the Loans & Leases sheet. Once this is pasted then the sheet will need to be calculated and the interest required will flow back to the Cash Flow sheet so that the next month’s withdrawal; thus, restarting the copy paste value for the next month’s cells.

 Sheets("Cash Flow").Select
    Range("E108").Select
    Selection.Copy
    Sheets("Loans & Leases").Select
    Range("C8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Cash Flow").Select
    Range("F108").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Loans & Leases").Select
    Range("D8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I have tried modifying the code below to work but have been unsuccessful.

 Dim x as integer
Application.Screenupdating = False
For each x = 8 to 148
Cells (90,x) = Cells(9,x)
Application.Calculate
Next x
Application.Screenupdating = True

The cells that need to be copied in sheet Cash Flow goes from E(5) to DT(124) and then pasted into the Loans & Leases sheet from C(3) to DR(121) one at a time so that the interest calculation is added back to the Cash Flow sheet so the next period’s loan withdraw is calculated correctly before the next copy paste value.

I cannot use the record macro as it generates too long of a code; I get an error message that the code is too long. I am not very good with VBA but do know enough to know what I need but just can’t really get there on my own so any help would be greatly appreciated.

Thanks
Luke

 

[luke1438]

Figured it out. Put the loan calculations onto the same page as the cash flow and then re-wrote the code as follows:

Sub loan()
Dim x As Integer
Dim y As Integer
Application.ScreenUpdating = False
For x = 3 To 121
Cells(130, x) = Cells(108, x)
Application.Calculate
Next x
For y = 3 To 121
Cells(135, y) = Cells(110, y)
Application.Calculate
Next y
Application.ScreenUpdating = True
End Sub

Thanks
Luke

 

[luke1438]

Ok, so I thought I had it worked out but it is not correct. The above code is going cell by cell but doing one row at a time and then following to the next row. So, here is the correct solution to make the cash flow and loan calculate each period.

Sub loan()
Dim x As Integer
Application.ScreenUpdating = False
For x = 3 To 121
Cells(130, x) = Cells(108, x)
Cells(135, x) = Cells(110, x)
Application.Calculate
Next x
Application.ScreenUpdating = True
End Sub