Hi everyone,

I've been trying for hours to get the following macro working, but it keeps giving me a "Subscript out of range" error. The error is at the Find expression, but I've been using the exact format I found from an example online, so I'm not sure what's going on.

As far as I can tell, it's not the sheet name, since I've tried it using the code name (used below) and the actual name (100% spelled correctly). I've also tried running it with different sheets and it's still the same issue. I also thought it might be related to the variables, but I've declared them according to what was in the example.


Sub CopyFormula()

Dim week As String
Dim volref As Range
Dim volcol As String

week = "Week " & Sheet2.Range("B5").Value

    Set volref = Sheet5.Range("E18:BD18").Find(What:=week, LookIn:=xlValues, _
    LookAt:=x1Whole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
            volcol = Split(volref.Address, "$")(1)
            Sheet5.Range("E19:E310").Copy _
            Destination:=Sheet5.Range(volcol & "19:" & volcol & "310")

End Sub

Just as a brief explanation of what I'm trying to achieve:

On the front page (Sheet2), the user selects a week number (e.g. "30") from a dropdown box in cell B5. They click an "Update" button which runs the macro.

The macro goes to a different worksheet (Sheet5 in this case) and searches for the week number as a string (e.g., "Week 30") in row 18 (from columns E to BD only). Note that Row 18 uses formulas to display the week numbers, which are called from a database. Then, once it finds the column for the appropriate week (e.g. column AB), it should copy cells E19 to E310 and paste it in that column in the same position (e.g. AB19 to AB310). Cells E19 to E310 contain formulas.

This process then needs to be completed across 4 other worksheets. The macro then ends.


This macro will be used on a weekly basis, so any help would be greatly appreciated!

Thanks,
aseret