Option Explicit
Sub testme()

Dim OldWkbk As Workbook
Dim NewWkbk As Workbook
Dim oWS As Worksheet
Dim nWS As Worksheet

Set OldWkbk = Workbooks("youroldworkbooknamehere.xls")
Set NewWkbk = Workbooks("yournewworkbooknamehere.xls")

For Each oWS In OldWkbk.Worksheets
Set nWS = Nothing
On Error Resume Next
Set nWS = NewWkbk.Worksheets(oWS.Name)
On Error GoTo 0

If nWS Is Nothing Then
MsgBox oWS.Name & " doesn't exist in " & NewWkbk.Name
Else
nWS.Range("C7").Value = oWS.Range("g40").Value
End If

Next oWS

End Sub


Untested, but it did compile.



On 09/26/2011 19:39, rech wrote:
I have 2 workbooks: OLD and NEW. They each have over 100 sheets
(most sheets are the same but some are different). I want to set the
value of cell C7 in the NEW workbook to the value from cell G40 in the
OLD workbook. I'd like open both workbooks then run a macro that runs
through every sheet in NEW and captures the sheet name then matches it
to the same sheet in the OLD workbook, copies the value from G40, then
pastes it into the NEW workbook C7.

Thanks so much for any help!!

--
Dave Peterson