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.
I have tried modifying the code below to work but have been unsuccessful.
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
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.
VBA Code:
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.
VBA Code:
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