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

luke1438

Board Regular
Joined
Nov 1, 2004
Messages
144
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.

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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

luke1438

Board Regular
Joined
Nov 1, 2004
Messages
144
Figured it out. Put the loan calculations onto the same page as the cash flow and then re-wrote the code as follows:

VBA Code:
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

Board Regular
Joined
Nov 1, 2004
Messages
144
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,854
Messages
5,574,673
Members
412,610
Latest member
bluedusty
Top