VBA Search Macro works in 2007 wont work when using 2003

[kmkelley]

I have the below code that works fine when I run it on my computer using 2007. When my associates try to run it using 2003 it doesnt work. I can't figure out what I need to change in order for it to work across the board.

here is the code:

Sub ClientUpdate()

Dim MySheet As String, MyBook As String, IMName As String
Dim MyNum As Integer
    IMName = Range("A1").Value
    MySheet = Left(IMName, 31)
    MyNum = Range("A15").Value
    MyBook = ActiveWorkbook.Name
            
    Windows("New Open Funds.xls").Activate
    Sheets("Project 3").Select
    Range("A1").Select
    Cells.Find(What:=IMName, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, 5).Range("A1:A" & MyNum).Select
    Selection.Copy
    Windows(MyBook).Activate
    Sheets(MySheet).Select
    Range("B17").Select
    ActiveSheet.Paste

End Sub

END OF CODE

Essentially what it is doing is searching an excel file for a value and selecting a specific number of cells from the offset of finding that value. There are several variables that have to pull from the orignal sheet that is running the macro and I need those variable to be pull from the sheet so that I can have different values on different sheets. It works on 2007 as I said but I cant get it to work with 2003. No idea why.

I am relatively new to VBA and have worked this out based on previous forum questions and utilizing the macro recorder on 2007.

Thanks for your help.

[kmkelley]

Could this have to do with my data file being in Read Only or Compatability Mode for the other users?

I need them to be able to utilize the macro when the data file is in read only. Is this possible?

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[kmkelley]

I've updated the code setup. Sorry for the confusion. Any ideas why this isn't working?

[romperstomper]

Something like this:

Sub ClientUpdate()

    Dim MySheet As String, MyBook As Workbook, IMName As String
    Dim SourceSheet As Worksheet
    Dim MyNum As Integer
    Dim rngFound As Range
    
    IMName = Range("A1").Value
    MySheet = left(IMName, 31)
    MyNum = Range("A15").Value
    Set MyBook = ActiveWorkbook
            
    Set SourceSheet = Workbooks("New Open Funds.xls").Sheets("Project 3")
    
    Set rngFound = SourceSheet.Cells.Find(What:=IMName, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
    If Not rngFound Is Nothing Then
        rngFound.Offset(0, 5).Range("A1:A" & MyNum).Copy Destination:=MyBook.Sheets(MySheet).Range("B17")
    End If
End Sub

Note: we generally need more information than "doesn't work". In what way does it fail - error message, do nothing, do the wrong thing or something else?

[kmkelley]

That works. Thank you

[kmkelley]

The new VBA code works when I am using it. However once I save it down and then reopen to run again I get a "subscript out of range" error. I have the New Open Funds.xls open so i don't know why I am getting the error. Ideas?

[kmkelley]

It errors out on this section of the code.

Set SourceSheet = Workbooks("New Open Funds.xls").Sheets("Project 3")

[romperstomper]

If the workbook is open in the same instance of Excel, then you would only get that error if the workbook name is wrong or the sheet name is.