Code - monthly portfolio update

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!

I didn't really have a chance to check the code entirely, but this line seems suspect:

Range(iCountCol + 1 & "3").Select

It will likely be a problem because iCountCol + 1 is a number. For example, if iCountcol = 10, your code would be:

Range(93).Select

About where the error occurs, I did not have any idea. Normally I program in Excel 2003, but yesterday I was using 2007. Maybe the settings were different or something, but it never showed which parts of the code needed to be debugged.

I changed the file format to 1997-2003 compatible, and the code worked! I modified it a little, and this is what I have now:

Sub HistoricalUpdate()
    Dim iCountCol, iFunds As Integer
    Dim strDate As String
    Set myRangeHist = Worksheets("Historical Balance").Range("3:3")
    iCountCol = WorksheetFunction.CountA(myRangeHist)
    Set myRangeFunds = Worksheets("Details").Range("C:C")
    iFunds = WorksheetFunction.CountA(myRangeFunds)
    
    strDate = InputBox(Prompt:="Date of Valuation", _
          Title:="Date", Default:="Date")
    Cells(1, iCountCol + 2) = strDate
    Worksheets("Details").Select
    Range("C3:D" & iFunds + 2).Select
    Selection.Copy
    Worksheets("Historical Balance").Select
    Cells(3, iCountCol + 1).Select
    Selection.PasteSpecial 12
    Cells(2, iCountCol + 1) = "Balance"
    Cells(2, iCountCol + 1).Font.Bold = True
    Cells(2, iCountCol + 2) = "Percentage"
    Cells(2, iCountCol + 2).Font.Bold = True
    Cells(iFunds + 2, iCountCol + 2).ClearContents
    
    
End Sub

Would anybody be able to tell me why the code did not work in the Excel 2007 format, but did work in the 2003 format? We are switching over to 2007 versions, and I don't want to have to go through these problems again.

Forgot to add, thanks for all the help so far!