Subscript out of range error

[CaptainKen]

I made a small change to simply skip/remove F4, F20:F24, F94:F101 from the original working rangeBudget as shown below.

Original rangeBudget array on Line 43 no error, works.
Modified rangeBudget array on Line 39 gives error.

Range F81:F:90 on the Months sheet is the last highlighted range when I step through the code and within the rangeMonths H81:S90 and H94:S101 are never copied. Then I get error "subscript out of range". When you review the two ranges, the range that works actually contains the modified range that skips specific ranges. In testing, if I remove H81:S90 and H94:S101 from line 39 rangeMonths I get no error. I'm lost on how simply omitting some cells from an existing range contained in the original array can cause the error?

I have a main xlsm workbook with vba in ThisWorkbook object to copy specific ranges of cells from another xlsx workbook. The only difference between the two workbooks is that the target with the vba has the xlsm extension and the source has the xlsx extension. I just doubled checked that they were the same by saving the target xlsm workbook as the xlxs. After doing this, I entered some test dollar amounts in the source workbook. So this confirms the ranges exists in both.

NOTE: Be sure to change sourceFilePath to your file location before testing.

Target.xlsm

Source.xlsx

Sub CopyDataFromClosedWorkbook()
    Dim sourceFilePath As String
    Dim sourceWorkbook As Workbook
    Dim sourceWorksheet As Worksheet
    Dim destinationWorksheet As Worksheet
    Dim rangeMonths As Variant
    Dim rangeBudget As Variant
    Dim i As Integer
    Dim sourceRangeMonths As Range
    Dim sourceRangeBudget As Range
    Dim destinationRangeMonths As Range
    Dim destinationRangeBudget As Range

    ' Set the file path of the workbook to copy data from
    sourceFilePath = "D:\OneDrive\Documents\Budget Stuff\Monthly Budget BEFORE Retirement - NEW.xlsx"

    ' Set the destination worksheet in the current workbook
    Set destinationWorksheet = ThisWorkbook.Sheets("Months") ' Change "Months" to your destination sheet name

    ' Define the same source and destination ranges to use for BOTH workbooks
    rangeMonths = Array("H4:S8", "H10:S16", "H20:S24", "H26:S37", "H39:S46", "H48:S54", _
                        "H56:S60", "H62:S72", "H74:S79", "H81:S90", "H94:S101")

    ' This range SKIPS F4, F20:F24, F94:F101, however it generates "subscript out of range" error
    rangeBudget = Array("F5:F8", "F10:F16", "F26:F37", "F39:F46", "F48:F54", _
                        "F56:F60", "F62:F72", "F74:F79", "F81:F90")

    ' This is the working range with no errors
    'rangeBudget = Array("F4:F8", "F10:F16", "F20:F24", "F26:F37", "F39:F46", "F48:F54", _
                            "F56:F60", "F62:F72", "F74:F79", "F81:F90", "F94:F101")

    ' Open the source workbook without displaying it
    Set sourceWorkbook = Workbooks.Open(sourceFilePath, ReadOnly:=True)

    ' Loop through each range and copy data from the source to the destination
    For i = LBound(rangeMonths) To UBound(rangeMonths)
        ' Set the source worksheet in the source workbook
        Set sourceWorksheet = sourceWorkbook.Sheets("Months") ' Change "Months" to your source sheet name
        
        ' Set the source ranges in the source workbook
        Set sourceRangeMonths = sourceWorksheet.Range(rangeMonths(i))
        Set sourceRangeBudget = sourceWorksheet.Range(rangeBudget(i))

        ' Set the destination ranges in the current workbook
        Set destinationRangeMonths = destinationWorksheet.Range(rangeMonths(i))
        Set destinationRangeBudget = destinationWorksheet.Range(rangeBudget(i))

        ' Suppress the alert message
        Application.DisplayAlerts = False

        ' Copy the data from the source ranges to the destination ranges
        sourceRangeMonths.Copy destinationRangeMonths
        sourceRangeBudget.Copy destinationRangeBudget
        
        ' Restore alert settings
        Application.DisplayAlerts = True
    Next i

    ' Close the source workbook without saving changes
    sourceWorkbook.Close SaveChanges:=False

    ' Optionally, you can activate the destination worksheet
    destinationWorksheet.Activate

    ' Optionally, you can display a message indicating the copy is complete
    MsgBox "Data copied successfully from the closed workbook."

End Sub

 

[Domenic]

I just edited my post, so please make sure that you're reading my edited version...

 

[CaptainKen]

Yeah baby that was it! Thank you!

 

[Domenic]

That's great, glad I could help.

Cheers!