Code to open workbook and change value in a specific cell

I think something like this would work better. I couldn't test because I don't have your directory and files, but if it doesn't work let me know which line give an error.

Sub ChangeDateValues()
Dim myDir As String, fn As String
myDir = "Z:\My Documents\Analyst Recommendations\Top Analysts\Analysts Results\JP Morgan\Test\"
fn = Dir(myDir & "*.xlsm")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        Worksheets("Sheet1").Range("A6:A7").Copy
        Workbooks.Open (myDir & fn)
        Sheets(20).Range("B5:B6").Paste
        Workbooks(fn).Close True 'If you want to save the paste, this must be true
    End If
    fn = Dir
Loop
End Sub

Unfortunately, I often get mixed up with all the requirements for the Paste method vs PasteSpecial. This should work, but some of it may be unnecessary:

Sub ChangeDateValues()
Dim myDir As String, fn As String, strWS as String
myDir = "Z:\My Documents\Analyst Recommendations\Top Analysts\Analysts Results\JP Morgan\Test\"
fn = Dir(myDir & "*.xlsm")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        Worksheets("Sheet1").Range("A6:A7").Copy
        Workbooks.Open (myDir & fn)
        strWS = Activesheet.Name
        Sheets(20).Activate
        ActiveSheet.Cells(5, 2).Activate
        ActiveCell.Paste
        Sheets(strWS).Activate
        Workbooks(fn).Close True 'If you want to save the paste, this must be true
    End If
    fn = Dir
Loop
End Sub

If this is overkill a guru please correct me with what is needed, thanks.

Let's try to simplify it:

Sub ChangeDateValues()
Dim myDir As String, fn As String
myDir = "Z:\My Documents\Analyst Recommendations\Top Analysts\Analysts Results\JP Morgan\Test\"
fn = Dir(myDir & "*.xlsm")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        Workbooks.Open (myDir & fn)
        ThisWorkbook.Activate
        Sheets(20).Activate
        Worksheets("Sheet1").Range("A6:A7").Copy Destination:=Workbooks(fn).Sheets(20).Cells(5, 2)
        Workbooks(fn).Close True 'If you want to save the paste, this must be true
    End If
    fn = Dir
Loop
End Sub

Hi everyone,

We can shorten Dave's code slightly again (I think!?) to read as below, with the aim being to minimise the repeated actions within the loop (ie activating the "master" workbook each time).

Sub ChangeDateValues()
Dim myDir As String, fn As String
myDir = "Z:\My Documents\Analyst Recommendations\Top Analysts\Analysts Results\JP Morgan\Test\"
fn = Dir(myDir & "*.xlsm")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        Workbooks.Open (myDir & fn)
        ThisWorkbook.Worksheets("Sheet1").Range("A6:A7").Copy Destination:=Workbooks(fn).Sheets(20).Cells(5, 2)
        Workbooks(fn).Close True 'If you want to save the paste, this must be true
    End If
    fn = Dir
Loop
End Sub

If you only want the values to be transferred & you aren't concerned about formatting etc, you can probably (untested!) use...

Sub ChangeDateValues()
Dim myDir As String, fn As String
myDir = "Z:\My Documents\Analyst Recommendations\Top Analysts\Analysts Results\JP Morgan\Test\"
fn = Dir(myDir & "*.xlsm")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        Workbooks.Open (myDir & fn)
'is my range conversion right?
Workbooks(fn).Sheets(20).range("B5:B6").value = ThisWorkbook.Worksheets("Sheet1").Range("A6:A7").value
'or the value2 property
Workbooks(fn).Sheets(20).range("B5:B6").value2 = ThisWorkbook.Worksheets("Sheet1").Range("A6:A7").value2
        Workbooks(fn).Close True 'If you want to save the paste, this must be true
    End If
    fn = Dir
Loop
End Sub

Rob

If the macro is in the workbook that you are currently running code from, you just have to put RunResults on a line while the workbook you want to run it in is active. If the macro is in the workbook that you have opened, you have to do something like Application.Run "MacroBook!MacroName" .
Just Google excel vba run macro another workbook for details.

It should be:

Application.Run fn & "!RunResults"

Thanks for the help Rob, I just couldn't quite nail it!

hi all,

Dave/Rhudgins
You were so close, all I've offered is a couple of small changes

Rhudgins,
What does the "RunResults" macro do?
To use someone else's words...

Application.Run is synchronous. The call to Application.Run("Macro_1") will not return until the macro has finished executing. That said, if the macro starts an asynchronous process, then you might get the effect you describe. What do the macros do, and what evidence do you have that the macros are running in parallel? – Gary McGill Nov 9 '09 at 20:44

Originally Posted by toryb

...you could both define your range as an object, and your source workbooks as objects ..... that will speed up your code, I believe?
...

Toryb,
If the range & the source workbooks were being referred to repeatedly, then, yes, I'd agree that defining them explicitly* would be likely to speed up the code. However, in this example, they are only referred to once.
*Also, rather than defining them as "objects", I'd suggest defining as the variable types that they are (ie "as Range" & "as Workbook").

Rob

Try putting your actual workbook name in single quotes like this:

Application.Run "'" & fn & "'!RunResults"