Hi all, I have a document that lists the accounts of my company's portfolio. I want to write a macro that copies the fund balances and percentage of total portfolio, and paste that information in another sheet where I am putting all my end-of-the-month values.

The funds are listed in sheet "Details" in A3:A22. The current balances for the individual funds in C3:C22. The current % of total portfolio of each fund is in D3:D22.

The historical balances are listed in sheet "Historical Balances". Each month, I want to paste as values the cells Details!B3:C22 into the next available column in the sheet "Historical Balances", and starting the 3rd row down (so, for the next month, the data will be pasted in Historical Data!F3:F22).

Here's the code I have written so far:

Sub HistoricalUpdate()

    Dim iCountCol, iFunds As Integer
    Set MyRangeHist = Worksheets("Historical Balance").Range("A3:IV3")
    iCountCol = Application.WorksheetFunction.CountA(MyRangeHist)
    Set MyRangeDetails = Worksheets("Details").Range("C:C")
    iFunds = Application.WorksheetFunction.CountA(MyRangeDetails) - 2
        
    Sheets("Details").Select
    Range("C3:D" & iFunds).Select
    Selection.Copy
    Sheets("Historical Balance").Select
    Range(iCountCol + 1 & "3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

End Sub
The code hasn't worked, and is now giving me a Run Time Error 9 Subscript Out of Range. Can anyone see what's wrong with my code? Thanks!